The Very Hidden Sheet in Excel
14 Mar 2010All 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.
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.
Thanks for sharing!
Thanks for sharing!
You are welcome!
You are very welcome. Thanks for reading and I hope you found it useful.
Regards,
Kiran
Post new comment