How to Hide and Unhide Sheets Based on Cell Value

[nhinx]

Dear All Excel Experts,

I would like to seek help from you to provide me a VBA that will hide and unhide the specific sheets i want. For example I have a sheets named "Data","Cat1", "Cat2", "Cat3","Dog1", "Dog2" and "Dog3" then whenever i input the word "Dog" in Cell "D4" it will hide (VeryHidden) the sheets named "Cat1", "Cat2", "Cat3". Then when I input the word "Cat" in Cell "D4" it will now unhide the sheets "Cat1", "Cat2", "Cat3" and then it will hide (VeryHidden) the sheets named "Dog1", "Dog2" and "Dog3". So Whenever I changed the name in Cell "D4" in sheet "Data" it will hide and unhide it vice versa.

Hope you could help me with this one.

Thanks

 

[Snakehips]

@nhinx Try like the below pasted into the code module of sheet 'Data'

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ws As Worksheet
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name = "Data" Then GoTo Skip:
    If LCase(Ws.Name) Like LCase(Target) & "*" Then
        Ws.Visible = xlSheetVisible
        Else
        Ws.Visible = xlSheetVeryHidden
    End If
Skip:
    Next Ws      
End Sub

HTH

 

[nhinx]

@nhinx Try like the below pasted into the code module of sheet 'Data'

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ws As Worksheet
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name = "Data" Then GoTo Skip:
    If LCase(Ws.Name) Like LCase(Target) & "*" Then
        Ws.Visible = xlSheetVisible
        Else
        Ws.Visible = xlSheetVeryHidden
    End If
Skip:
    Next Ws     
End Sub

HTH

Hi Snakehips,

Thanks for the code, however, the procedures hide all the sheets other than the Data sheet. What I need is to hide the specific sheets and show other sheets when I input a certain text in that column.

Regards,

Nhinx

 

[Snakehips]

For me, it always retains Data sheet as visible. Then testing with sheets like 'Cat1', 'Cat566' 'Dogsbreakfast', 'Dog2'
If D4 = 'Dog' it will show any sheet that has is named 'Dog*******" and hide all others.
Then if D4 = Cat' it will show any sheet that has is named 'Cat*******" and hide all others.

Is this not what you want?

 

[nhinx]

For me, it always retains Data sheet as visible. Then testing with sheets like 'Cat1', 'Cat566' 'Dogsbreakfast', 'Dog2'
If D4 = 'Dog' it will show any sheet that has is named 'Dog*******" and hide all others.
Then if D4 = Cat' it will show any sheet that has is named 'Cat*******" and hide all others.

Is this not what you want?

Hi Snakehips,

Yes, that is correct

 

[Snakehips]

@nhinx With that code, all sheets bar Data will be hidden if the entry into D4 does not match any sheet name.

The following code should address that and will unhide ALL sheets if there is no match at all for D4

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Check As Boolean
Dim Ws As Worksheet
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
     'Check that D4 entry applies to at least one sheet name
    Check = False
    For Each Ws In ThisWorkbook.Worksheets
        If LCase(Ws.Name) Like LCase(Target) & "*" Then
            Check = True
            Exit For
    End If
    Next Ws
    
    If Check = False Then  'the entry in D4 does not match any sheet so show all
    For Each Ws In ThisWorkbook.Worksheets
        Ws.Visible = xlSheetVisible
   Next Ws
   Application.ScreenUpdating = True
   Exit Sub
   End If
    
    'otherwise hide all bar Data and sheets starting with the entry in D4
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name = "Data" Then GoTo Skip:
    If LCase(Ws.Name) Like LCase(Target) & "*" Then
        Ws.Visible = xlSheetVisible
        Else
        Ws.Visible = xlSheetVeryHidden
    End If
Skip:
    Next Ws
   
    Application.ScreenUpdating = True
        
End Sub

 

[nhinx]

@nhinx With that code, all sheets bar Data will be hidden if the entry into D4 does not match any sheet name.

The following code should address that and will unhide ALL sheets if there is no match at all for D4

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Check As Boolean
Dim Ws As Worksheet
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub
   
    Application.ScreenUpdating = False
     'Check that D4 entry applies to at least one sheet name
    Check = False
    For Each Ws In ThisWorkbook.Worksheets
        If LCase(Ws.Name) Like LCase(Target) & "*" Then
            Check = True
            Exit For
    End If
    Next Ws
   
    If Check = False Then  'the entry in D4 does not match any sheet so show all
    For Each Ws In ThisWorkbook.Worksheets
        Ws.Visible = xlSheetVisible
   Next Ws
   Application.ScreenUpdating = True
   Exit Sub
   End If
   
    'otherwise hide all bar Data and sheets starting with the entry in D4
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name = "Data" Then GoTo Skip:
    If LCase(Ws.Name) Like LCase(Target) & "*" Then
        Ws.Visible = xlSheetVisible
        Else
        Ws.Visible = xlSheetVeryHidden
    End If
Skip:
    Next Ws
  
    Application.ScreenUpdating = True
       
End Sub

Hi Snakehips,

Thanks for the Code, I tried it but didn't work.

Regards,

 

[Snakehips]

Then I obviously have not managed to interpret your need correctly.
If you are able to clarify your needs compared to the results of the code then I will help if I can.

 

[nhinx]

Then I obviously have not managed to interpret your need correctly.
If you are able to clarify your needs compared to the results of the code then I will help if I can.

Hi Snakehips,

The code below is working but when I change the sheet numbering it's no longer working


Private Sub Worksheet_Change(ByVal target As Range)

Select Case Worksheets("Data").Range("D4").Value

Case "Dog"
Worksheets("Dog 1").Visible = xlSheetVisible
Worksheets("Dog 2").Visible = xlSheetVisible
Worksheets("Dog 3").Visible = xlSheetVisible
Worksheets("Cat 1").Visible = xlSheetVeryHidden
Worksheets("Cat 2").Visible = xlSheetVeryHidden
Worksheets("Cat 3").Visible = xlSheetVeryHidden

Case "Cat"
Worksheets("Dog 1").Visible = xlSheetVisible
Worksheets("Dog 2").Visible = xlSheetVisible
Worksheets("Dog 3").Visible = xlSheetVisible
Worksheets("Cat 1").Visible = xlSheetVeryHidden
Worksheets("Cat 2").Visible = xlSheetVeryHidden
Worksheets("Cat 3").Visible = xlSheetVeryHidden

End Select
End Sub

 

[Snakehips]

Same for both Cat & Dog??? I'm assuming that is a typo?