Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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 -- HTH... Jim Thomlinson "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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 -- Rick (MVP - Excel) "mrsviqt" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "mrsviqt" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "mrsviqt" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
Thank you all for your help! I'll try these when I get back to the office on
Monday. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "mrsviqt" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
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! "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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide multiple worksheets
This line is where you'd make the changes:
Case Is = LCase(MainWks.Name), lcase("sheet 1") mrsviqt wrote: 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! "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unhide multiple worksheets in workbook | Excel Discussion (Misc queries) | |||
Unhide Multiple Worksheets At Once | Excel Discussion (Misc queries) | |||
unhide multiple worksheets in one go | Excel Programming | |||
unhide multiple worksheets in one go | Excel Programming | |||
How can I unhide columns on multiple worksheets in same workbook? | Excel Programming |