Using VBA to hide all sheets except X,Y, and Z

[Raesu]

Hello all,


I'm making a pretty large workbook with over 20 worksheets, and would like to have different sets of worksheets show depending on which option button is selected on the main sheet. I have done this, but it is very inefficient and difficult to update when new sheets are added. Current I have something like this attached to each option button:


Option button changes the value of cell A1, If A1 =....

Dim Sh as Worksheet


For Each Sh in Worksheets(Array("A","B","C","All of the sheets I want, 5 or 6"))

Sh.visible = True

Next Sh

For Each Sh in Worksheets(Array("All the other sheets, about 20 different ones")

Sh.Visible = False

Next Sh


I there a way to make my A, B, C and other desired sheets visible, and use an else to make all the others sheets invisible? It looks like a mess having so many sheet names typed out that I want hidden.


I tried doing that originally, but I couldn't get it to work and I needed something to send out. Any advice is appreciated, thank you.

 

[Luke M]

Still need to list exceptions, but only once:

[pre]

Sub HideSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "A" Or ws.Name = "B" Or ws.Name = "C" Then
ws.Visible = True
Else
ws.Visible = False
End If
Next ws
End Sub

[/pre]

 

[Raesu]

Looks good, I knew it was something simple like that but just couldn't get it to compile. I was missing the .Name for Sh...thanks so much!

 

[Raesu]

This either hides all my worksheets, or causes Excel to crash. Whats wrong with this?

[pre]

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "X", "Y", "Z", "XX", "YY"
Sh.Visible = True
Case Else
Sh.Visible = False
End Select
Next ws

[/pre]

EDIT: I figured it out...not sure why it was an issue though. The sheet containing the option buttons was being hidden, then unhidden after that select loop. Making this sheet always shown fixed it. (Added the name of the sheet to the ws.Visible Case).