So, when you enter or Select "All" in Range "C3" you want all your sheets shown is that correct?
But Excel does not allow you to hide all sheets. There must be one sheet visible.
And what are the other values you might want to select or enter into Range ("C3")
I assume you're using a Data Validation list in Range("C3")
So, will you only be using "All" or a specific sheet name?
Show me the code you're now using. You said:
"I found a solution to hide tabs that I don't need to show"
Hi there,
Thank you for your reply and apologies for making the rookie mistakes.
Yes, on the menu tab is a dropdown list that comes from a data validation list on the Lists tab.
The list is just the tab names Summary, Testing 1, 2, 3...
My problem is that when I select Testing 1, all other tabs except the Menu and Testing 1 tab are hidden. Same goes for any other tab, it always only shows Menu plus selected tab.
What I would like to do is have a macro/code etc. that would give me the option of showing
1. Menu tab
2. Summary tab
3. Testing 1/2/3/4
i.e., a minimum of 3 tabs, if there is a possibility to select and show more testing tabs (as these are from different departments, and I need to calculate testing cost for multiple departments) that would make this XL sheet so much more useful.
Now the code I found is below (text in bold below is accompanying text/comment from the originator.
Menu Worksheet Code
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelectType").Address Then
Select Case Target.Value
Case " All"
ShowAllSheets
Case ""
'do nothing
Case Else
ShowSelSheets
'do nothing
End Select
End If
End Sub
Show Sheets Macros
The following macros are stored in a regular code module.
The ShowAllSheets macro runs when "ALL" is selected, and makes all the sheets visible.
VBA Code:
Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub
The ShowSelSheets macro runs if any other option is selected, and it shows only the Menu sheet, and any sheet that has the selected text in its name.
VBA Code:
Sub ShowSelSheets()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Menu").Range("SelectType").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Menu" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
XL2BB below
| selectsheettype.xlsm |
|---|
|
|---|
| A | B | C | D | E |
|---|
| 1 | | | | | |
|---|
| 2 | | | Select a Sheet | | |
|---|
| 3 | | | All | | |
|---|
| 4 | | | | | |
|---|
| 5 | | | | | |
|---|
| 6 | | | | | |
|---|
| 7 | | | | | |
|---|
| 8 | | | | | |
|---|
| 9 | | | | | |
|---|
|
|---|
