Home » Blogs » Kiran's blog

The Very Hidden Sheet in Excel

14 Mar 2010
Posted by Kiran

All of us know that Microsoft Excel allows us to hide certain worksheets from view. Hiding sheets helps us in ensuring that any background lookup data or reference data that you want to utilize in your spreadsheets remain neatly tucked away from general view.

You may also want to prevent users from viewing certain numbers that are used in calculation on your spreadsheets. Hiding worksheets simply makes the workbook clutter free and helps readers focus on the spreadsheets that really matter.

The only disadvantage with simply hiding your worksheets is that it is equally easy to unhide them. Any Excel user worth his salt knows how to find and unhide a hidden sheet. To prevent users from unhiding your worksheets, you may choose to protect the workbook. However when you protect a workbook, you are also preventing the users from a myriad of other functionality that they may genuinely require.

The answer may be the Very Hidden sheet in Excel. I discovered this while reading about Excel features on the Internet.

What is the Very Hidden sheet?
It is basically a sheet that is permanently hidden from view. Users cannot view or manipulate data on that sheet in any way. Data on very hidden sheets remain available for use within the workbook and can be used in formulas on other visible sheets.

The advantage of using very hidden sheet is that there is no easy way to unhide the sheet — other than programmatically.

How do you set a sheet to Very Hidden?
You might have already guessed — the only way to do this is, again, programmatically. And, that is the reason this isn't something the average Excel user is expected to be able to do.

What does the code look like?
A sample code that could be used is given below.

 
Sub sVeryHideSheet(ByRef objSht As Excel.Worksheet)
     objSht.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
End Sub

Sub sShowSheet(ByRef objSht As Excel.Worksheet)
     objSht.Visible = Excel.XlSheetVisibility.xlSheetVisible
End Sub

Sub sHideSheet(ByRef objSht As Excel.Worksheet)
     objSht.Visible = Excel.XlSheetVisibility.xlSheetHidden
End Sub

Sub Main()
     Dim objApp As Excel.Application
     Dim objWrkBk As Excel.Workbook
     Dim objWrkSht As Excel.Worksheet

     objWrkBk = objApp.Workbooks.Open("C:\MyExcelFile.xls")

     'Hide Sheets
     sVeryHideSheet(objWrkBk.Sheets(1))
     sHideSheet(objWrkBk.Sheets(2))

     'Show Sheets
     sShowSheet(objWrkBk.Sheets(1))
     sShowSheet(objWrkBk.Sheets(2))
End Sub
 

I have also written a small utility that lets users easily set selected sheets as very hidden and then unhide them too as required. Feel free to download the executable and the source code. Please note that this code was initially written using VB6 and then upgraded to VB.NET using the Visual Studio upgrade tool. I haven't tested the solution after the upgrade since I haven't had the need to. If you plan to put it to use, you may need to do some tweaking to the code to get it working.

Tags: