How to Hide All Sheets Using VBA

How to Hide All Sheets Using VBA

  • Post author:
  • Post category:VBA

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. Excel for visual basic can be used to perform tasks and automation by use of knowledge in visual basic programming language. We can thus create codes that will enable us to hide all other worksheets using VBA and only make us remain with the currently active worksheet. This requires us to activate the developer functionality in our excel project to make use of various tools in coding VBA. These codes are then run or executed via the Macro feature found under the developer tabs which effects execution of the codes.

How to hide all sheets using VBA except the active one

 

Step 1: Navigate through your computer by clicking the start button for those who have windows operating systems installed.

Click on MS Excel to run it. You can optionally search on the search bar.


Step 2: Excel with then load and display its window. Select the document you wish to open and wait for it to load.

Step 3: After Waiting for the document to load a ribbon with all kinds of excel commands which open to default as the Home tab.

Choose the Developer tab to continue. However, this tab might not be present by default and it must be activated through customization of the ribbon.


Step 4: After Looking for the developer tab find out that it does not exist please do the following. Press “File” tab.

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

From this list of commands that are actually descending from top to bottom select “Options” located towards the bottom left.


Step 5: 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 one that has 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 then select the OK button to effect the changes.

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


Click “customize ribbon” option to open yet another window and select developer checkbox as shown below. Then click OK. Developer tab should now be activated.

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


Step 7: Now navigate to the group named “code” and select “Visual Basic” icon.

This icon is usually located near the macros tab.

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

Step 8: 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 which holds our important codes that we need to run so as we can hide the worksheets. Dim this is just below the method stands for Dimension which is used to initialize a variable called Wrksheet.

The “AS” keyword, tells the computer that the Variable is of Worksheet type.

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

The “sub” and “End sub” shows the beginning and end of code.

Step 9: Now save the code work and resume back to excel by close 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.

Step 10: Select on hideworksheets and click “RUN” on the macro dialog box.

After Selecting 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.