View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ker_01 ker_01 is offline
external usenet poster
 
Posts: 395
Default Syntax to match sheet by codename

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