Syntax to match sheet by codename

[ker_01]

I'm attempting to show/hide sheets via code, so I can see all the sheets when
updating a workbook, then hide the source sheets so my users just see the
output sheets.

unhiding every sheet is easy;
Sub ShowAllSheets()
Dim sht As Worksheet
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End Sub

But when trying to re-hide, I need to exclude the user sheets. I know how to
do this by setting up an array of every sheet to be hidden, but that is a lot
of extra code. Here is my attempt to hide everything except three user sheets
(I've tried several variations) with no luck. I keep getting a 438 runtime
error, "object does not support this property or method"

Sub HideAllSheets()
Dim sht As Worksheet

Dim shta As Worksheet
Dim shtb As Worksheet
Dim shtc As Worksheet

Set shta = Sheet2
Set shtb = Sheet4
Set shtc = Sheet17

For Each sht In Excel.ActiveWorkbook.Worksheets
If (sht = shta) Or (sht = shtb) Or (sht = shtc) Then '<--- this is
where it fails
'do nothing
Else
sht.Visible = xlSheetHidden
End If
Next
End Sub

 

[tompl]

I would dump the three variables you set up as they are redundant and use
this language:

If sht.Name = "Sheet2" Or sht.Name = "Sheet4" or sht.Name = “Sheet17†Then

I have not seen the use of parenthesis where you have them.

Tom

 

[ker_01]

Sometimes I use parens just out of habit- when I have more complicated logic,
it helps me keep track, but certainly not necessary here.

I need to refence by codename, not sheetname- my users may change the
sheetname so I don't want to hardcode those into my VBA. That put me on the
right track though- "sht.codename" does what I was looking for.

Thank you,
Keith

 

[Anthony]

try sht.name=shta.name

 

[Dave Peterson]

Maybe...

Option Explicit
Sub HideAllSheets()
Dim sht As Worksheet

'make sure at least one sheet is visible to start
Sheet2.Visible = xlSheetVisible

For Each sht In Excel.ActiveWorkbook.Worksheets
Select Case LCase(sht.CodeName)
'all lower case
Case Is = "sheet2", "sheet4", "sheet17"
'do nothing
'or
sht.Visible = xlSheetVisible
Case Else
sht.Visible = xlSheetHidden
End Select
Next sht

End Sub

ps. If you're using codenames, then you might as well change the codenames to
something that means something.

Open excel
open your workbook
Open the VBE
Hit ctrl-r to see the project explorer
select one of the sheets
hit ctrl-f4 to see its properties
Change the (Name) property to something significant.

Case Is = "pricing", "details", "costs"

May make the code easier to understand that sheet2, sheet4 and sheet17.