How to Hide All Sheets Using VBA

Excel for visual basic can be used to perform tasks and automation by using knowledge in a visual basic programming language. Excel is very much integrated to the extent that we can perform some actions through actual programming. Codes intended to manipulate certain sections of the workbook can be coded at ease using the developer option that most of us are not used to.

We can thus create codes that will enable us to hide all other worksheets using VBA and only make us remain with the current Worksheet. These codes are then run or executed via the Macro feature found under the developer tabs, which affects the execution of the codes. This requires us to activate the developer functionality in our excel project to use various tools in coding VBA.

How to hide all sheets using VBA

Step 1:

Open your excel workbook with multiple active sheets. Choose the Developer tab to continue. However, this tab might not be present by default, and it must be activated through the customization of the ribbon.

Step 2: After Looking for the developer tab to find out that it does not exist, please do the following. Press the "File" tab.

This will open a window with a pane containing a list of commands on its left. Some commands include Save, save as, open, close, info, recent, new, print, save &send, Help, Options, and Exit.

From this list of commands descending from top to bottom, select "Options" located towards the bottom left.

Step 3: Another window with Excel options will appear with a similar layout as the previous one.

Select the "Customize Ribbon" option. A list box with main tabs to be included and checkboxes against their names will be displayed towards the right side of the window.

Click on the developer checkbox to turn on developer capabilities and select the OK button to effect the changes.

The changes should now be saved by pressing the OK button, which will close the customization window. You will then be taken back to the excel worksheet that you were working on with the developer tab enabled.

Step 4: Now click the developer tab on the ribbon.

Step 5: Navigate to the " code " group and select the "Visual Basic" icon.

This icon is usually located near the macros tab.

A blank window named Microsoft Visual Basic for Applications" will appear.

Step 6: Create a new module or optionally click sheet1 and Type the following code as it appears in the code editor as below:

The code to hide tabs

Sub HideWorksheets()
Dim Wrksheet As Worksheet
For Each Wrksheet In ThisWorkbook.Worksheets
If Wrksheet.Name <> ThisWorkbook.ActiveSheet.Name Then
Wrksheet.Visible = xlSheetHidden
End If
Next Wrksheet
End Sub

The code while in Excel VBA text editor.

To explain the code, the "HideWorksheets()" is a method that holds the important codes that we need to run so that we can hide the worksheets. This is just below the method stands for Dimension, which is used to initialize a Worksheet variable.

The "AS" keyword tells the computer that the Variable is Worksheet type.

The "For each" line code is used to loop continuously or repeatedly run the codes on hiding the worksheets, while the "worksheet.visible=xlSheetHidden" code sets the status of all other inactive tabs to hidden.

The "sub" and "End sub" show the beginning and end of the code.

Step 7: Now save the code work and resume back to excel by closing the dialog boxes that had appeared. Navigate to the Developer tab and select macros

A dialog will show up with the title macro. The dialog will list all available macros, including the name HideWorksheets. Note that you need to save it as a Macro-enabled workbook. (.xlsm)

Step 8: Click on macro on the developer's tab 

After Selecting the macro, a dialog will be displayed.

After selecting our macro from the list and clicking "RUN," the macro will run the codes that we created, and this will hide all other tabs and leave only the active sheet tab.