Author: Oscar Cronquist Article last updated on May 29, 2020

Hides worksheets programmatically

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 right-click with the mouse on a worksheet tab, then click "Hide".

Note, hidden worksheets can easily be made visible again. Right-click on any visible worksheet tab located at the bottom of your Excel window.

A pop-up menu appears, click "Unhide".

Hides worksheets programmatically unhide worksheets

A dialog box shows up, click the worksheet you want to make visible again. Click OK button to apply changes.

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

Hides worksheet programmatically

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?

Hides worksheet programmatically VB Editor

  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. Click "Insert" on the top menu.
  3. Click "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, click 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.
Note, make sure you save the workbook with file extension *.xlsm (macro-enabled workbook) which allows you to save macros as well.

Back to top

How to run the macro?

Hides worksheet programmatically Start a 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. Click the macro name you want to start.
  5. Click "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

Hides worksheet programmatically Link button to macro

It is possible to create a button on your worksheet that runs the macro when the user clicks 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. Click the "Insert" button on the "Developer" tab, a pop-up menu appears.
  3. Click the button below "Form Controls" on the pop-up menu.
  4. Click 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.
    Hides worksheet programmatically assign macro
  8. Click the macro name to select it.
  9. Click button "OK" to apply.

Hides worksheet programmatically button

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.

Click 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.

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

Select the button, then click 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

Click a button to show or hide specific worksheets

The animated image above shows a button linked to a macro that hides Sheet2 and Sheet3 if the user clicks 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.

Hides worksheet programmatically error

Run-time error '9':

Subscript out of range

Back to top

Macro hides/shows multiple worksheets based on an array

Hides worksheet programmatically 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

Back to top