Please help!

I am trying to very hide every worksheet in this workbook except for 3- one called RegionalControl, RegionalHelp, and one containing the word competition. Every time the code executes, it tells me it is unable to set the visible property. Here is my code:

Dim curwkbk As Workbook
Set curwkbk = ThisWorkbook
Dim wks As Worksheet
Dim wksname As String

For Each wks In curwkbk.Worksheets
wksname = wks.Name
If LCase(wks.Name) Like "*total*" Then
Worksheets(wksname).Visible = xlSheetVeryHidden
ElseIf LCase(wks.Name) Like "*Competition*" Then
'nothing
ElseIf wks.Name = "RegionalControl" Then
'nothing
ElseIf wks.Name = "RegionalHelp" Then
'nothing
Else
Worksheets(wksname).Visible = xlSheetVeryHidden
End If

Next wks

Thanks for any help you can give me!

Hi Dumbass,

As written, your code will hide any sheet with Competition (any case) as
LCase anything can never match *Competition*
Therefore, change:
ElseIf LCase(wks.Name) Like "*Competition*" Then
to:
ElseIf LCase(wks.Name) Like "*competition*" Then

To allow for possible case errors in the other excluded sheets, it is
probably worth applying a similar case condition for them too, so that your
code becomes:

A small point, but the line:

If LCase(wks.Name) Like "*total*" Then
Worksheets(wksname).Visible = xlSheetVeryHidden

seems redundant.

Finally, if the intention is that the exclusion sheets should be visible, I
would apply an explicit visible = true condition in case one or more has
been hidden (by the user?).

Incorporating these comments, your code becomes:

Sub Tester()
Dim curwkbk As Workbook
Set curwkbk = ThisWorkbook
Dim wks As Worksheet
Dim wksname As String

For Each wks In curwkbk.Worksheets
wksname = wks.Name
If LCase(wks.Name) Like "*competition*" Then
Worksheets(wksname).Visible = True
ElseIf LCase(wks.Name) = "regionalcontrol" Then
Worksheets(wksname).Visible = True
ElseIf LCase(wks.Name) = "regionalhelp" Then
Worksheets(wksname).Visible = True
Else
Worksheets(wksname).Visible = xlSheetVeryHidden
End If
Next wks

End Sub

Running your code (adjusted for para #1), I could only replicate your error
if none of the three exclusion sheets was *visible*. Running the adjusted
code, I can only replicate your error if none of the three exclusion sheets
*exists*.

---
Regards,
Norman




"Dumbass" wrote in message
...
Please help!

I am trying to very hide every worksheet in this workbook except for 3-
one called RegionalControl, RegionalHelp, and one containing the word
competition. Every time the code executes, it tells me it is unable to set
the visible property. Here is my code:

Dim curwkbk As Workbook
Set curwkbk = ThisWorkbook
Dim wks As Worksheet
Dim wksname As String

For Each wks In curwkbk.Worksheets
wksname = wks.Name
If LCase(wks.Name) Like "*total*" Then
Worksheets(wksname).Visible = xlSheetVeryHidden
ElseIf LCase(wks.Name) Like "*Competition*" Then
'nothing
ElseIf wks.Name = "RegionalControl" Then
'nothing
ElseIf wks.Name = "RegionalHelp" Then
'nothing
Else
Worksheets(wksname).Visible = xlSheetVeryHidden
End If

Next wks

Thanks for any help you can give me!

Try:

Sub HideSheets()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If Not LCase(wks.Name) Like "*competition*" _
And Not wks.Name Like "Regional*" Then _
wks.Visible = xlSheetVeryHidden
Next wks
End Sub

Regards,
Greg


-----Original Message-----
Please help!

I am trying to very hide every worksheet in this workbook
except for 3- one called RegionalControl, RegionalHelp,
and one containing the word competition. Every time the
code executes, it tells me it is unable to set the visible
property. Here is my code:

Dim curwkbk As Workbook
Set curwkbk = ThisWorkbook
Dim wks As Worksheet
Dim wksname As String

For Each wks In curwkbk.Worksheets
wksname = wks.Name
If LCase(wks.Name) Like "*total*" Then
Worksheets(wksname).Visible = xlSheetVeryHidden
ElseIf LCase(wks.Name) Like "*Competition*" Then
'nothing
ElseIf wks.Name = "RegionalControl" Then
'nothing
ElseIf wks.Name = "RegionalHelp" Then
'nothing
Else
Worksheets(wksname).Visible = xlSheetVeryHidden
End If

Next wks

Thanks for any help you can give me!


.