Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with vb.veryhidden
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with vb.veryhidden
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with vb.veryhidden
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
Unable to password-protect "VeryHidden" worksheet | New Users to Excel | |||
Sheet hidden and VeryHidden | Excel Discussion (Misc queries) | |||
Accessing a "veryhidden" sheet | Excel Programming | |||
Stuck myself - can't bring back "VeryHidden" | Excel Programming |