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.

Fig 1

excel vba hide a sheet completely

excel excel vba hide a sheet completely

excel excel excel vba hide a sheet completely

Fig 4

excel excel excel excel vba hide a sheet completely

excel excel excel excel excel vba hide a sheet completely

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).

excel excel excel excel excel excel vba hide a sheet completely

b) Click on the sheet to be hidden (Sheet1). Click on "View" .

excel excel excel excel excel excel excel vba hide a sheet completely

c) In the Properties Window for "Sheet1", set the "Visible" property to "xlSheetVeryHidden". Close the Visual Basic Editor.

excel excel excel excel excel excel excel excel vba hide a sheet completely

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.

excel excel excel excel excel excel excel excel excel vba hide a sheet completely

 

You can find similar Excel Questions and Answer hereunder

1) How to change desktop background in Excel

2) How can I save a WorkSheet as a new WorkBook using VBA?

3) How to disable ability to insert Rows and Columns in Excel (using VBA)?

4) Determine if hyperlinks are valid in Excel

5) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

6) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

7) How can I dynamically add series to an existing chart using VBA?

8) How can I set up ListBox using VBA to allow users to select multiple values?

9) Vba list all files in a folder in Excel

10) How can I set non-contiguous print area using VBA?

11) How do I restrict user entry in ActiveX Text Box to just numeric values?

12) How do i put double quotes in a string in vba in Excel

13) Line break in vba message box in Excel

14) Vba code to password protect workbook in Excel

15) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?