Show / Hide Sheets based on Field Value

jbunn

New Member
Joined
Feb 6, 2013
Messages
30
Hi -

I am trying to show / hide worksheets that start with a specific character based on a List selection. The following code will show or hide the worksheets however I can't figure out how to run the code based on a cell value.

For example, if B15 says "Show Detail" then show worksheets starting with "P". Consequently, if B15 says "Hide Detail, then hide worksheets starting with "P"

Sub ShowHideProject()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "P*" And ws.Visible = False Then
ws.Visible = xlSheetVisible
Else
If ws.Name Like "P*" And ws.Visible = True Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,900
Try something like this:
Code:
Sub ShowHideProject()
    Dim ws As Worksheet
    If Range("B15") = "Show Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "P*" And ws.Visible = False Then
                ws.Visible = True
            End If
        Next ws
    
    ElseIf Range("B15") = "Hide Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "P*" And ws.Visible = True Then
                ws.Visible = False
            End If
        Next ws
    End If
End Sub
If you want the hiding/unhiding to happen automatically when you enter "Show/Hide Detail" in B15, then the macro can be modified as a Worksheet_Change event. Please let me know if this is what you would like. Cheers!
 

jbunn

New Member
Joined
Feb 6, 2013
Messages
30
Thanks Mumps!. Yes, I would like it to be a Change Event. Also, do I have to reference the worksheet name that corresponds to B15?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,900
Place this macro into the worksheet code module where you will be entering data in B15. Don't put it into a regular module. I would suggest that you put a validation list in B15 where you can choose "Hide Detail" or "Show Detail" from a drop down. This would eliminate spelling errors.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B15")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    If Target = "Show Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "P*" And ws.Visible = False Then
                ws.Visible = True
            End If
        Next ws
    
    ElseIf Target = "Hide Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "P*" And ws.Visible = True Then
                ws.Visible = False
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub
 

jbunn

New Member
Joined
Feb 6, 2013
Messages
30
Nice! That worked. Thank You!!
 

jbunn

New Member
Joined
Feb 6, 2013
Messages
30
OK, one last question. How do I specify multiple targets

For example
If B1 = Show Detail, then show worksheets targeting with M
If B15 = Show Detail, then show worksheets targeting with P (Got this already)
If B20 = Show Detail, then show worksheets targeting with U

Huge Thanks!
 

jbunn

New Member
Joined
Feb 6, 2013
Messages
30
Example correction

For example
If B1 = Show Detail, then show worksheets names starting with M
If B15 = Show Detail, then show worksheets names starting with P (Got this already)
If B20 = Show Detail, then show worksheets names starting with U
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,900
This should do it:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1,B15,B20")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    If Target.Address = "$B$1" And Target = "Show Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "M*" And ws.Visible = False Then
                ws.Visible = True
            End If
        Next ws
    
    ElseIf Target.Address = "$B$1" And Target = "Hide Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "M*" And ws.Visible = True Then
                ws.Visible = False
            End If
        Next ws
    End If
    
    If Target.Address = "$B$15" And Target = "Show Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "P*" And ws.Visible = False Then
                ws.Visible = True
            End If
        Next ws
    
    ElseIf Target.Address = "$B$15" And Target = "Hide Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "P*" And ws.Visible = True Then
                ws.Visible = False
            End If
        Next ws
    End If
    
    If Target.Address = "$B$20" And Target = "Show Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "U*" And ws.Visible = False Then
                ws.Visible = True
            End If
        Next ws
    
    ElseIf Target.Address = "$B$20" And Target = "Hide Detail" Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name Like "U*" And ws.Visible = True Then
                ws.Visible = False
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,360
Messages
5,444,017
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top