Author: Oscar Cronquist Article last updated on September 20, 2017

Click a button to show or hide specific worksheets

Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7.


Here is how I did it.

Create a button

  1. Go to Developer tab on the ribbon
  2. Click "Insert" button
    developer tab - insert button1
  3. Click "Button" button
  4. Drag on sheet to make a button
  5. Click on button to rename

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
  1. Go to VB Editor (Alt + F11)
  2. Click Insert on the menu
  3. Click Module
    vb editor - insert a module
  4. Paste code to module
  5. Exit VB Editor and return to excel

Assign macro to button

  1. Right click on button
  2. Click "Assign macro..."
  3. Select macro ShowHideWorksheets
  4. Click OK

Download excel *.xlsm file

Click a button to make specific worksheets hidden or visible (vba).xlsm