Unhide multiple worksheets

  • Thread starter Thread starter mrsviqt
  • Start date Start date
M

mrsviqt

I have a file that contains 144 sheets. Thyey are all named as follows
:"storenumber department year" (example: 111 abc 2007). Each store number
has 6 sheets (2 departments and three different years). What I would like is
to have a sheet named "Main" that is always unhidden. I would like to have a
spot on that sheet that asks "Which store would you like to view", "Which
department would you like to view", or "Which year would you like to view".
It will unhide the sheets based on the selection that is made. For example,
if store 111 is chosen, all of store 111's 6 sheets will be unhidden. Is
this even possible?
Thanks in advance.
 
Here is some example code for you to try...

Sub test()
Call UnHideSheets("2007", 3) 'matching 2007 as the 3rd part of the name
End Sub

Public Sub UnHideSheets(ByVal strToMatch As String, ByVal lngElement As Long)
Dim wks As Worksheet
Dim strName() As String

For Each wks In Worksheets
strName = Split(wks.Name, " ")
If UBound(strName) = 2 Then
wks.Visible = xlSheetHidden
If strToMatch = strName(lngElement - 1) Then wks.Visible =
xlSheetVisible
End If
Next wks
End Sub
 
Yep.

I would add another (hidden) sheet (or on an out of the way location on the Main
sheet).

This sheet would hold all the possible store numbers in column A.
It would hold all the department numbers in column B.
And all the possible years in column C.

Then I would name each of these lists using a dynamic range name.
Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

I'd use the names: List_StoreNumber, List_Dept, List_Year


Then I'd add Data|Validation to 3 cells on the Main sheet that use these 3
lists.
http://contextures.com/xlDataVal01.html

And I'd give each of those cells a nice name (Input_StoreNumber, Input_Dept,
Input_Year).

And finally, I'd add a button from the Forms toolbar. This button would have a
macro assigned to it that would check to see if any of those cells are used. If
at least one is, then it would cycle through the worksheets looking for matches
based on the stuff that was specified.

If there are other sheets visible when the user hits the button, should those
sheets be hidden--or kept visible?

You ready to try???

Option Explicit
Sub ShowSheets()

Dim MainWks As Worksheet
Dim InputDept As String
Dim InputStoreNumber As String
Dim InputYear As String
Dim SheetNamePattern As String
Dim wks As Worksheet
Dim HowManyMadeVisible As Long

Set MainWks = ActiveSheet

'pick up those choices
With MainWks
InputDept = .Range("Input_Dept")
InputStoreNumber = .Range("Input_StoreNumber")
InputYear = .Range("Input_Year")
End With

If InputDept = "" _
And InputStoreNumber = "" _
And InputYear = "" Then
'nothing chosen
MsgBox "Please make some choices!"
Exit Sub
End If

'replace missing values with wildcards (*)
'to match anything
If InputDept = "" Then
InputDept = "*"
End If

If InputStoreNumber = "" Then
InputStoreNumber = "*"
End If

If InputYear = "" Then
InputYear = "*"
End If

SheetNamePattern = InputStoreNumber & " " & InputDept & " " & InputYear

HowManyMadeVisible = 0
For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
'add any sheets that shouldn't be touched to this
'list of sheetnames
Case Is = LCase(MainWks.Name), "sheetwithlistsonit"
'do nothing, keep it visible or hidden
Case Else
If LCase(wks.Name) Like LCase(SheetNamePattern) Then
HowManyMadeVisible = HowManyMadeVisible + 1
wks.Visible = xlSheetVisible
Else
'maybe??????
wks.Visible = xlSheetHidden
End If
End Select
Next wks

If HowManyMadeVisible = 0 Then
MsgBox "There were no worksheet names that matched your pattern"
Else
MsgBox HowManyMadeVisible & " worksheets made visible"
End If

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Wow Dave... They must be paying you by the word... Nice solution though. I
always like reading your solutions. In this case it just took a long time.
 
I haven't tested this code below, but I believe it will do what you want.
Note that I assumed the answers to your 3 questions (storenumber,
department, year) are located in N1, N2 and N3 respectively. The code allow
your user to specify answer 1, 2 or all 3 questions and it should unhide
sheets matching those criteria while hiding the rest.

Sub SheetUnhider()
Dim WS As Worksheet, Find As String
If Range("N1").Value = "" Then
Find = "* "
Else
Find = Range("N1").Value & " "
End If
If Range("N2").Value = "" Then
Find = Find & "* "
Else
Find = Range("N1").Value & " "
End If
If Range("N3").Value = "" Then
Find = Find & "*"
Else
Find = Find & Range("N1").Value
End If
For Each WS In Worksheets
If WS.Name <> "Main" Then WS.Visible = UCase(WS.Name) Like UCase(Find)
Next
End Sub
 
Note... the code, as I posted it, assumes it is being run from the Main
sheet (the sheet with the questions on it); otherwise those Range("Nx")
references will need to be qualified.
 
This is a much more compact version of my previously posted code (and I
included the Main worksheet references in it)...

Sub SheetUnhider()
Dim X As Long, WS As Worksheet, Find As String
For X = 1 To 3
If Worksheets("Main").Range("N" & X).Value = "" Then
Find = Find & "* "
Else
Find = Find & Worksheets("Main").Range("N" & X).Value & " "
End If
Next
For Each WS In Worksheets
If WS.Name <> "Main" Then
WS.Visible = UCase(RTrim(WS.Name)) Like UCase(Find)
End If
Next
End Sub
 
Sometimes it's difficult to know where to start. When a co-worker asked me
about a problem, my first question may have been: "Is your computer on?"

If it was in person, I'd duck!
 
Dave,
Ok, it works. I'm having a couple of problems though. There is a sheet
called "Sheet 1" and I need it to stay open. Also, I would like a macro that
automatically runs on close that hides all sheets except "Main" and "Sheet
1".
Everything else works great.
Thanks in advance for your help!
 
This line is where you'd make the changes:

Case Is = LCase(MainWks.Name), lcase("sheet 1")
 
Back
Top