How to pass word protect one sheet of a workbook
- Thread starter GN0001
- Start date
vijaySharma
Member
Guity,
Open the VB Editor by pressing ALT+F11 from the keyboard or go to the Developer Tab and then click on VB Editor.
If not already visible, go to the View menu and Click on Project Explorer.
Within the project explorer locate the Excel sheet that you want to hide...
Click on the sheet and then press F4 to get the Properties window.
Now locate the Visible Property for this sheet and make that xlVeryHidden.
To unhide you will have to come back here and set the property as xlVisible.
~VijaySharma
Open the VB Editor by pressing ALT+F11 from the keyboard or go to the Developer Tab and then click on VB Editor.
If not already visible, go to the View menu and Click on Project Explorer.
Within the project explorer locate the Excel sheet that you want to hide...
Click on the sheet and then press F4 to get the Properties window.
Now locate the Visible Property for this sheet and make that xlVeryHidden.
To unhide you will have to come back here and set the property as xlVisible.
~VijaySharma
vijaySharma
Member
Hi Shajan,
As stated above, you need to repeat the process. goto VB Editor and then locate the Sheet2 on project explorer.
Click on the same and then Press F4 to get the properties window, and then set the Visible property to xlVisible.
HTH
~VijaySharma
As stated above, you need to repeat the process. goto VB Editor and then locate the Sheet2 on project explorer.
Click on the same and then Press F4 to get the properties window, and then set the Visible property to xlVisible.
HTH
~VijaySharma
Shajan,
What if some body knows that there is a hidden sheet by seeing your formulas or mere knowledge?
They can also go to VBA Editer and unhide it!!
You may need to password protect the project OR passowrd protect the workbook (not worksheet). In second option ensure you check structure, it would not only disable anyone from unhiding they cannot even insert, delete or move any sheets from this workbook.
Regards,
Prasad
What if some body knows that there is a hidden sheet by seeing your formulas or mere knowledge?
They can also go to VBA Editer and unhide it!!
You may need to password protect the project OR passowrd protect the workbook (not worksheet). In second option ensure you check structure, it would not only disable anyone from unhiding they cannot even insert, delete or move any sheets from this workbook.
Regards,
Prasad
It is very interesting Thank you all for the all the input,
1- I think I need to follow Vijay direction and then place a password on the workbook. I need to make dynamic charts and do you think If I password protect my workbook, the end user would be able to interact with drop down menus?
2- If I pass word protect the workbook, how can I check structure referring to (ensure you check structure?
Thank you very much,
1- I think I need to follow Vijay direction and then place a password on the workbook. I need to make dynamic charts and do you think If I password protect my workbook, the end user would be able to interact with drop down menus?
2- If I pass word protect the workbook, how can I check structure referring to (ensure you check structure?
Thank you very much,
Ok I understood which check box I need to check. But still I appreciate the answer to the number 1. Also, I think If I want to restrict the end user from changing the cells or inserting row, pass protecting the workbook doesn't do anything, I need to password protect the sheet first. Any thoughts? or tips?
Thanks
Thanks
In answer to #1, protecting the workbook should not prohibit the use of drop downs w/ you dynamic charts.
Yes, if you want to prevent end user from changing cells, you'll want to protect the sheet as well. Note that the workbook/worksheet passwords are VERY easy to crack. (a macro can do it in under 5 minutes). If you place a password on the VB project (again, in the VB explorer window, right click on your project, project properties. Enable a password) is "slightly" more secure. It can still be beaten using a HEX editor or something.
Does that help?
Yes, if you want to prevent end user from changing cells, you'll want to protect the sheet as well. Note that the workbook/worksheet passwords are VERY easy to crack. (a macro can do it in under 5 minutes). If you place a password on the VB project (again, in the VB explorer window, right click on your project, project properties. Enable a password) is "slightly" more secure. It can still be beaten using a HEX editor or something.
Does that help?
Luke, I followed what you said and right click on the sheet and VBA project properties, and I went under second tab, checked lock cell and I entered a password. after doing this, I could do whatever I wanted to do with my cells and sheets. In fact the password didn't work. Can you please explain a little bit more? I may have not done it properly.
Thank you for the help.
Thank you for the help.
vijaySharma
Member
Guity,
What you have done is to Password protect the VBA code only. This is not the same as protecting your worksheet.
Follow these steps.
1. Go to the sheet you want to protect.
2. Assuming Cell A1 and A2 contain your dropdown options, you want the end user to be able to change them. Select both these cells and Right Click ... Format cells.
3. Now go to the Protection Tab, and remove the Check mark from Locked.
4. Click on OK to close the Format Cells dialog box.
5. Now password protect you sheet.
Post this you will be able to change the Cells A1 and A2 only.... rest of the sheet you cannot type anything.
HTH
~VijaySharma
What you have done is to Password protect the VBA code only. This is not the same as protecting your worksheet.
Follow these steps.
1. Go to the sheet you want to protect.
2. Assuming Cell A1 and A2 contain your dropdown options, you want the end user to be able to change them. Select both these cells and Right Click ... Format cells.
3. Now go to the Protection Tab, and remove the Check mark from Locked.
4. Click on OK to close the Format Cells dialog box.
5. Now password protect you sheet.
Post this you will be able to change the Cells A1 and A2 only.... rest of the sheet you cannot type anything.
HTH
~VijaySharma
I need end user not to be able to make any changes in the sheet, not only the drop down menu, but also, all the cells in the sheet. So how can I make the end user not interact with any cells, except drop down menu to pick up a value from the drop down menu?
Luck directs me to VBA sheet and properties windows and tells me enable password, in VB window, the only thing can be protected is the VB code as far as I understand.
When I use protect sheet and workbook from the menu, I can make changes to the sheets and workbook.
Thank you for the help.
Luck directs me to VBA sheet and properties windows and tells me enable password, in VB window, the only thing can be protected is the VB code as far as I understand.
When I use protect sheet and workbook from the menu, I can make changes to the sheets and workbook.
Thank you for the help.
vijaySharma
Member
Guity,
Did you try the steps on my previous post... this will help you to lock the sheet and prevent the user from changing anything.
There are 3 items mixed here and causing confusion.
1. How to Protect my Worksheet
2. How to protect my VBA Code
3. How to make my sheet very hidden, so that end user cannot unhide this.
All three have been answered, so please go through the entire post and let us know if you need more help on this.
~VijaySharma
Did you try the steps on my previous post... this will help you to lock the sheet and prevent the user from changing anything.
There are 3 items mixed here and causing confusion.
1. How to Protect my Worksheet
2. How to protect my VBA Code
3. How to make my sheet very hidden, so that end user cannot unhide this.
All three have been answered, so please go through the entire post and let us know if you need more help on this.
~VijaySharma