Forum Discussion
Excel not password protecting VBA page properly...
I basically want the sheets hidden (which I know how to do - via Excel, right click sheet and hide) but then I only want it to be possible to retrieve them after entering a password in the VBA editor. Currently, when I hide the sheets and then set the password I can still just go into the VBA editor and unhide them as it is not prompting me for a password.
You can use VBA to set the sheets as "Very Hidden," which means they can't be unhidden through Excel's normal interface (not even via the "Unhide" option). Here's how:
- Open the VBA Editor (press Alt + F11).
- On the left side, in the project explorer, select the sheet you want to hide.
- In the "Properties" pane (press F4 if it's not visible), find the Visible property.
- Change the Visible property from -1 - xlSheetVisible to 2 - xlSheetVeryHidden.
Next, you should protect the VBA project with a password to prevent anyone from modifying the sheet settings without access to the code:
- In the VBA Editor, click Tools > VBAProject Properties.
- In the window that appears, go to the Protection tab.
- Check the box Lock project for viewing.
- Enter and confirm a password.
- Click OK and close the VBA Editor.
- Save and close the Excel file. (XLSM)
Now, when someone tries to open the VBA Editor (by pressing Alt + F11), they will be prompted to enter the password before they can view the code or change the visibility of the sheets.
- It's so strange, because I have tried exactly this, setting the sheets to "VeryHidden" and then setting the password, saving, closing and re-opening but still it does not work.
I am wondering whether my Excel is broken.
Is there a workaround do you know? Like a VBA script I can write that will hide/lock the sheets somehow?A silly question, but I need to know: Are you saving your spreadsheet macro-enabled (XLSM)?
Here are some examples you can use:
https://trumpexcel.com/excel-vba/hide-unhide-sheet/
https://trumpexcel.com/hide-worksheet/
so it seems you have 2 different things here:
a) VBA protection - this only protects the VBA code from being editted or not
b) workbook protection - this is needed to prevent user from changing the overall structure of the workbook including hiding and unhiding sheets
as to why your VBA protection is not working I'm not sure but even if the protection is on you can still use the immediate window to hide or unhide a sheet or run macros you wrote to do the same.
to hide a sheet and stop it from showing you need to hide the sheet then enable workbook protection
this is under the Review tab:
EDIT: BTW I did some testing and if you try to turn on the VBA protection but do NOT have any macros/VBA code in the file, excel ignores it (I guess because there is no code to protect) Again, VBA protection does NOT protect from executing code, only from reading and/or modifying code that exists (and I wouldn't entrust that protection to anything too valuable/risky. e.g. don't embed your back account info in there or your patent pending next gen AI code and believe it is safe from malicious prying eyes)
