asked on

create toggle button to hide and unhide selected sheets

Hi,

I need some help creating a toggle button to hide and unhide various worksheets. The Sample File's Summary tab have two programs that show the total revenue and cost for each program. I'd like to have a toggle button for each program to hide and unhide the details associated with each program.

WM -> WM Details 1, WM Details 2, WM Details 3
SAM -> SAM Details 1, SAM Details 2

Thank you in advance!
Sample-File.xlsx

Bobby F

ASKER

I forgot to mention that the button should hide on first click and unhide on second.

SOLUTION

Flora Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

You can modify Flora's answer like this

Sub CommandHide()
Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    If ws.Visible = True Then
         ws.Visible = xlSheetHidden
    Else
        ws.Visible = xlSheetVisible
Next ws

End Sub

Open in new window

Flora Edwards

thanks Martin for making it more effective.

i am one of your students :)

i have created two button and assiged the macros to it. but i get the out of subscript 9 error. i could not spot the error what is it that causes the error. i checked the spelling of worksheets are also correct.

can you please check the attached file and advise on the error?
Sample-File.xlsm

Martin Liss

I don't know why the Array() doesn't work but this does.

Sub CommandHide()
Dim ws As Worksheet
For Each ws In Worksheets
    Select Case ws.Name
    Case "WM Details 1", "WM Details 2", "WM Details 3"
        If ws.Visible = True Then
             ws.Visible = xlSheetHidden
        Else
            ws.Visible = xlSheetVisible
        End If
    End Select
Next ws

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

thanks Martin.

Bobby F

ASKER

Great job guys!