Forum Discussion

KirkExcel's avatar
KirkExcel
Copper Contributor
Sep 10, 2024

Excel not password protecting VBA page properly...

Whenever I try to hide sheets and then password protect them in the VBA page it never works. Whenever I go back into the VBA page it just gives me immediate and full access without prompting me for a...
Excel
Macros and VBA
Hermes_Santos14's avatar
Hermes_Santos14
Copper Contributor
I'm sorry you haven't found a solution for your case yet.
Are you actually trying to hide the worksheet with VBA or hide the VBA codes in your workbook?
kirkbiddlecombe's avatar
kirkbiddlecombe
Copper Contributor
Sep 11, 2024

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.

  • Hermes_Santos14's avatar
    Hermes_Santos14
    Copper Contributor
    Sep 11, 2024

    kirkbiddlecombe 

     

    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:

    1. Open the VBA Editor (press Alt + F11).
    2. On the left side, in the project explorer, select the sheet you want to hide.
    3. In the "Properties" pane (press F4 if it's not visible), find the Visible property.
    4. 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:

    1. In the VBA Editor, click Tools > VBAProject Properties.
    2. In the window that appears, go to the Protection tab.
    3. Check the box Lock project for viewing.
    4. Enter and confirm a password.
    5. Click OK and close the VBA Editor.
    6. 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.

    • kirkbiddlecombe's avatar
      kirkbiddlecombe
      Copper Contributor
      Sep 11, 2024
      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?

Resources