Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Password protect columns in Excel? 3
- Thread starter Sugada
- Start date
-
3
- #2
DaleWatson123321
Programmer
Hi Sugada,
Fairly simple ... After you have hidden the columns, use Tools, Protection, Protect Sheet. In this window, there will be a position for entry of a password. Naturally, you will want to ensure that you do not forget or misplace your filed hard-copy of the password. Otherwise, you will be unable to later unprotect the sheet.
Another important point to appreciate... When you protect the sheet, ALL cells immediately become protected - unless cells or ranges of cells, or columns, or rows, have been previously "unlocked".
When you choose Format, Cells, Protection (from the menu), you will notice that all cells initially have a check-mark opposite "Locked". But this "Locked" format ONLY becomes active once the Sheet is Protected (with Tools, Protection, Protect Sheet).
Therefore, what you will want (need) to do PRIOR to protecting the sheet, is to format all those cells where you will want to give access to the end-user regarding editing or entering data. Depending upon your application, THIS part can be more of a challenge - i.e. to disable "Locked" for all those cells/ranges where the user will need to have access.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Fairly simple ... After you have hidden the columns, use Tools, Protection, Protect Sheet. In this window, there will be a position for entry of a password. Naturally, you will want to ensure that you do not forget or misplace your filed hard-copy of the password. Otherwise, you will be unable to later unprotect the sheet.
Another important point to appreciate... When you protect the sheet, ALL cells immediately become protected - unless cells or ranges of cells, or columns, or rows, have been previously "unlocked".
When you choose Format, Cells, Protection (from the menu), you will notice that all cells initially have a check-mark opposite "Locked". But this "Locked" format ONLY becomes active once the Sheet is Protected (with Tools, Protection, Protect Sheet).
Therefore, what you will want (need) to do PRIOR to protecting the sheet, is to format all those cells where you will want to give access to the end-user regarding editing or entering data. Depending upon your application, THIS part can be more of a challenge - i.e. to disable "Locked" for all those cells/ranges where the user will need to have access.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I have a related question. I've followed the directions above to hide a specific column that I don't want our customers to see and it works perfectly in Excel. However, if I import the spreadsheet into Access, I can view the hidden column.
Is there a way to maintain the wksht protection when importing the data into Access? I want them to be able to import just the non-hidden data into Access.
Thanks for your help.
Elaine
Is there a way to maintain the wksht protection when importing the data into Access? I want them to be able to import just the non-hidden data into Access.
Thanks for your help.
Elaine
DaleWatson123321
Programmer
Hi Elaine,
I will readily admit I'm not an expert at the "more secure" methods of protecting Excel, and whether this will prevent the hidden columns from being imported to Access.
So, hopefully someone will come through with some expertise in this area.
Actually, if you don't get a response here soon, you should consider submitting a SEPARATE posting, and possibly in the Access forum.
If, in the end, it turns out that it's NOT possible to prevent the hidden columns from being included, there is an option. This would involve setting up a relatively simple extraction routine in VBA to extract to a SEPARATE sheet those columns that you DO want the end-user to import into Access. If you do decide you want to use this method, let me know, and I can provide example files showing the VBA routines for "selectively" extracting data to separate sheets.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I will readily admit I'm not an expert at the "more secure" methods of protecting Excel, and whether this will prevent the hidden columns from being imported to Access.
So, hopefully someone will come through with some expertise in this area.
Actually, if you don't get a response here soon, you should consider submitting a SEPARATE posting, and possibly in the Access forum.
If, in the end, it turns out that it's NOT possible to prevent the hidden columns from being included, there is an option. This would involve setting up a relatively simple extraction routine in VBA to extract to a SEPARATE sheet those columns that you DO want the end-user to import into Access. If you do decide you want to use this method, let me know, and I can provide example files showing the VBA routines for "selectively" extracting data to separate sheets.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
DeLaMartre
Technical User
Though I have previously read about this technique which Dale descibes, his concise explanation in this instance has inspired me to put it into use.
Thanks very much for the tip, Dale! It deserves another star!
-Bob in California
Thanks very much for the tip, Dale! It deserves another star!
-Bob in California
- Status
Similar threads
- Locked
- Question