vba hide a sheet completely in Excel
Q167. In Excel, how can I hide a sheet completely from users (the sheet should not even appear in "Unhide" dialog box)?
For example, I would like to hide Sheet1 (Fig 1). If I right click on Sheet1 and click Hide as in Fig 2, the sheet gets hidden as in Fig 3.
However, when I click on "Unhide" as in Fig 4, "Sheet1" shows up in "Unhide" dialog box. Users can employ this option to access the hidden sheet.
I do not even want to leave a clue regarding hidden sheet as I plan to include configuration information that cannot be altered anytime.
To do it in Excel, here is the answer:
a) Click on "Visual Basic" under "Developer". Visual Basic Editor comes up as shown below.
Note: If the Developer tab is missing in Ribbon, you can add it using the Customize Ribbon tab of the Excel Options dialog box.
(File->Options->Customize Ribbon-> under Main tab on the right ensure "Developer" is checked).
b) Click on the sheet to be hidden (Sheet1). Click on "View" .
c) In the Properties Window for "Sheet1", set the "Visible" property to "xlSheetVeryHidden". Close the Visual Basic Editor.
d) When right clicking on Sheet Name, in the menu the Unhide option is greyed out as if there are no sheets hidden. This way there is no access to Sheet1 for end users.
Whenever edits are required for Sheet1, the owner of the sheet can set the visible property to "xlSheetVisible", edit Sheet1 and then reset the property back to "xlSheetVeryHidden" using Visual Basic Editor.