Problems with vb.veryhidden

[Guest]

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!

 

[Norman Jones]

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

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:

 

[Greg Wilson]

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: