Show / Hide Sheets based on Field Value

[jbunn]

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

 

[mumps]

Try something like this:

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]

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]

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.

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]

Nice! That worked. Thank You!!

 

[jbunn]

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]

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]

This should do it:

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

 

[jbunn]

Your Awesome. Thank!

 

[mumps]

My pleasure.