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!


Jim Thomlinson wrote:

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.
--
HTH...

Jim Thomlinson

"Dave Peterson" wrote:

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 he
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 he
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.)

mrsviqt wrote:

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.

--

Dave Peterson


--

Dave Peterson