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 FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bobby FAuthor Commented:
I forgot to mention that the button should hide on first click and unhide on second.
0
FloraCommented:
here is the two macros one to hide and second to unhide

change the sheet1, sheet2 sheet3 to your actual sheet names

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


End Sub
Sub CommandUNHide()
Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        ws.Visible = xlSheetVisible
    Next ws


End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FloraCommented:
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
0
Martin LissOlder than dirtCommented:
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

0
Martin LissOlder than dirtCommented:
And here is working Array() code
Sub CommandHideSm()
Dim wsName As Variant ' Must be a Variant 
For Each wsName In Array("SAM Details 1", "SAM Details 2")
    If Sheets(wsName).Visible = True Then
         Sheets(wsName).Visible = xlSheetHidden
    Else
        Sheets(wsName).Visible = xlSheetVisible
    End If
Next wsName

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraCommented:
thanks Martin.
0
Bobby FAuthor Commented:
Great job guys!
0
Bobby FAuthor Commented:
Hey guys, is there any way to stay on the Summary page after unhiding the sheets?  It keeps taking me to the third sheet when I unhide.
0
Martin LissOlder than dirtCommented:
Sure. Put Sheets("Summay").Activate at the end of the macro.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.