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.

Instructions

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

I recommend Mynda Treacy’s Excel Dashboard course

There’s a new craze around town and it’s called Excel Dashboard reports. Actually it’s not that new but it’s becoming a standard for reporting, and as a result it’s a must have skill for any Excel user who prepares reports.

This is why I’m excited to let you know about Mynda Treacy’s Excel Dashboard course.

The course is video based and available online 24/7. It comes with comprehensive Excel workbooks and several sample dashboards to keep. There’s also an option to download the videos, plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

Dashboards are an incredibly valuable tool in today's market for consultants, analysts and managers. But Excel doesn’t make it straightforward to build highly professional and interactive dashboards. That's why this type of training is crucial.

What members are saying about the course:
The previous classes have been a huge hit with many people saying how they love the cool techniques and how they've been able to impress their colleagues and clients by using them in all sorts of reports, not just dashboards.

I highly recommend the course but don’t take my word for it. You can read comments from past students and find out more here.

Bonus 20% Off
If you join the class by 24th October you can get it for 20% off plus I’ll include Merge Ranges Addin, just email me your receipt and I’ll send you my bonus.

So, do yourself a favour and check out the course.

Disclosure: I make a small commission for students who join Mynda’s course.

Also check out Mynda Treacy’s 30 chart tips (pdf)