Microsoft Excel - Hiding Excel Worksheets with VBA
Got another great question from a student in my Excel VBA UserForms course on Udemy.
I have built and Excel UserForm that allows users to interact with worksheets within a workbook. I don’t want the users to interact with the worksheets directly. How can I hide the worksheets from the users, but unhide them when I need to work with the worksheets?
Imagine you have an Excel workbook that contains various worksheet, one worksheet for each employee. In this example, we have 5 employees.
Each of these employees should only be able to interact with their specific worksheet. This will happen through the Excel UserForm. Excel worksheets can be hidden.
Right Click Worksheet Tab — Select Hide
The problem with this approach is users can just as easily unhide a worksheet.
Right Click any Worksheet Tab — Select Unhide
A more secure approach to hiding worksheets can be accomplished through VBA.
The simple sub procedure above will go through each worksheet, except the 1st worksheet, and hide it. Using the xlSheetVeryHidden value will block users from unhiding a worksheet through the main Excel interface. In order to unhide a worksheet you will need to use VBA to do so.
Excel requires that one worksheet is always visible in an Excel workbook, hence the reason way the first worksheet is not hidden. This is accomplish in the FOR… NEXT.. loop above by giving “x” a starting value of 2. This way the loop will start with worksheet #2, Worksheet(x).Visible.
This procedure could be called from the Workbook_Open Event in order to hide all the worksheets each time the workbook is opened.
A very similar procedure could be used to unhide the worksheets when needed.
The only difference between this procedure and the HideUserWS procedure is this line: Worksheets(x).Visible = True