This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet tabs at the bottom of the window.

To hide a worksheet manually press with right mouse button on with the mouse on a worksheet tab, then press with left mouse button on "Hide".

This article explains how to hide worksheets using Visual Basic for Applications (VBA). A macro contains VBA code. macros allow you to create very useful subroutines to minimize repetitive tasks etc.

Macro hides worksheet

This example demonstrates a macro that hides a specific worksheet.

'Name macro
Sub HideWorksheet()

'Hide worksheet named Sheet1
ActiveWorkbook.Worksheets("Sheet1").Visible = False

End Sub

To make Sheet1 visible again change False to True in the above macro.

Back to top

Where to put the code?

1. Press and hold shortcut key Alt, then press function key F11 once. This opens the Visual Basic Editor (VBE), shown in the image above.
   Release Alt key.
2. Press with left mouse button on "Insert" on the top menu.
3. Press with left mouse button on "Module" to create a module. This module will be shown in the Project Explorer and in this example, named Module1 which the image above also shows.
   The Project Explorer shows all open workbooks, press with left mouse button on the plus sign to see the contents of a workbook.
4. Copy the above code.
5. Paste to the module window, also shown in the image above.
6. Return to Excel.

Back to top

How to run the macro?

1. Press and hold Alt key on your keyboard.
2. Press function key F8 once. This opens the Macro dialog box, shown in the image above.
3. Release Alt key.
4. Press with left mouse button on the macro name you want to start.
5. Press with left mouse button on "Run" button to start the selected macro.

Macro toggles worksheet hidden/visible

The following macro hides worksheet Sheet1 if visible and shows Sheet1 if hidden.

 
'Name macro 
Sub HideWorksheet() 

'Make worksheet named Sheet1 hidden if visible and vice versa
ActiveWorkbook.Worksheets("Sheet1").Visible = Not ActiveWorkbook.Worksheets("Sheet1").Visible

End Sub 

Where to put the code?

How to start macro?

Back to top

Link macro to button

It is possible to create a button on your worksheet that runs the macro when the user press with left mouse button ons on it.

1. Go to tab "Developer" on the ribbon. If missing search the internet for "Show developer tab" and your Excel version to find instructions on how to enable it.
2. Press with left mouse button on the "Insert" button on the "Developer" tab, a pop-up menu appears.
3. Press with left mouse button on the button below "Form Controls" on the pop-up menu.
4. Press and hold with left mouse button where you want to place the button on the worksheet.
5. Drag with mouse to size the button.
6. Release the left mouse button.
7. A dialog box appears to assign a macro.
   
8. Press with left mouse button on the macro name to select it.
9. Press with left mouse button on button "OK" to apply.

The image above shows a button, the round circles around the box indicates it is selected. They are called sizing handles and lets you adjust the size once again if you like.

Press and hold with left mouse button on any of the sizing handles, then drag with the mouse to resize the button. Release the left mouse button when finished.

Press with left mouse button on anywhere outside the button to deselect. To select it again press and hold CTRL key and press with left mouse button on with left mouse button on it. You start the assigned macro if you press with left mouse button on it without pressing the CTRL key.

Select the button, then press with left mouse button on the button text. A prompt appears that allows you to edit the button text.

Back to top

Macro hides/show multiple worksheets based on values

The animated image above shows a button linked to a macro that hides Sheet2 and Sheet3 if the user press with left mouse button ons on the button. The worksheet names are specified in cell range B6:B7.

VBA Code

Sub ShowHideWorksheets()
Dim Cell As Range
For Each Cell In Range("B6:B7")
ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

The macro returns the following error if you try a worksheet name that doesn't exist in your workbook.

Run-time error '9':

Subscript out of range

Back to top

Macro hides/shows multiple worksheets based on an array

VBA Code

Sub ShowHideWorksheets()
arr = Array("Sheet2", "Sheet3")
For Each value In arr
ActiveWorkbook.Worksheets(value).Visible = Not ActiveWorkbook.Worksheets(value).Visible
Next value
End Sub

Back to top

Get the Excel file

Press with left mouse button on-a-button-to-make-specific-worksheets-hidden-or-visible-vba.xlsm

Back to top

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Worksheet category

List all hyperlinks in worksheet programmatically
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]

Excel categories

Comments (4)