When you are working with an Excel workbook, you might find it essential to hide worksheets. This is very useful when a worksheet contains a standard set of data or static data or those data that does not change so frequently. One example is a price table of goods or merchandise, hourly rate of workers, and others that you want it protected from an unwanted and accidental editing.
The normal or default way of hiding worksheets is to right click on the Sheet that you want to hide, in the context menu that appears, click on Hide. The right clicked sheet will be hidden from view.
However, even though the hidden sheet is no longer shown in the sheet tab, when the user right clicks on the worksheet tab, there is a command Unhide. Clicking on this command will give you a message box showing the worksheets that are hidden. There you can unhide the hidden worksheets.
If the data in the hidden sheets are confidential or critical, you might need to hide the worksheets but then, the Unhide button shall be unavailable so that people cannot easily Unhide sheets that you do not want to share with them.
The best and easy approach to this problem is through setting the sheet properties to “xlSheetVeryHidden”. Here are the steps:
- Open the Excel file
- Right click on the sheet that you want to hide
- Then click on View Code
- Here is my screen shot:
- In the VBA window that appears, locate and click on the “Properties” button to display the Properties window. Here is my screen shot:
- When you see that Properties box, click on the Sheet that you want to hide and the Properties box will display the Properties of the selected sheet
- Then at the bottom property called Visible, select the option “2 – xlSheetVeryHidden”
- Here is my screen shot:
- NOTE: When you selected Sheet 2 and set the Visible property to 2 – xlSheetVeryHidden, the selection will not remain in place because other sheets will then show up. That is, after you are done with Sheet 2, Sheet 3 will be selected so it may appear to you that your settings were gone. Select back Sheet 2 and see if the settings (VeryHidden settings) are there.
- Then click File => Close and return to Excel
- There you shall see that the sheets are hidden but when you right click, there is no UNHIDE command available.
- To undo this in your case? Just redo the process and set the Visible property back to Visible.
The procedures above were done using Microsoft Excel 2010 but these procedures still work with newer versions of Excel.