VBA to to selectively hide worksheets from drop down list

[Nikosan73]

I have a worksheet that acts as a Table of Contents, called "TOC".

There are dozens of worksheets behind it, which all have an identical format set of headers rows. One of those cells, cell B6, which contains a drop down lists of category, so that I can categorises each worksheet, like "Admin" , "Finance" etc.

I have created a script that dependant on the same dropdown list but this time in cell E3 on the "TOC" worksheet, shows only those worksheets that are of a certain category, for example "Admin". However the IF statements are hard coded in VBA as is the drop down lists, using the Data Validation functionality and a Range which lists the categories, called "Categories". It becomes cumbersome when I want to add a new category mainly because I have to add another IF statement.

I would like to update it so that the "worksheet hide script" can then work dynamically against the selection in cell E3 on the "TOC" worksheet.

 

[Nikosan73]

I am almost there but I cannot get the worksheets to unhide when the Target cell is blank

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet

'Only monitor Cell C4
If Target.Address = Range("$C$4").Address Then

'If Target is Blank then apply Autofilter.
If IsEmpty(Range("c4").Value) = True Then
Range("b5:g5").AutoFilter

Else

'If Target is not Blank apply AdvancedFiler

Range("$B$5:$G$30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("$c$3:$c$4"), Unique:=False

For Each ws In Worksheets
If ws.Name <> "TOC" Or ws.Range("A2") <> Worksheets("TOC").Range("C4").Value Then
ws.Visible = 0

If ws.Name = "TOC" Or ws.Range("A2") = Worksheets("TOC").Range("C4").Value Or IsEmpty(Sheets("TOC").Range("$C$4")) Then
ws.Visible = -1

End If
End If
Next ws


End If
End If
End Function

 

[Nikosan73]

Obviously End Sub not End Function

 

[Nikosan73]

Finally did it - a bit cumbersome, so happy for comments on improving it:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet​

'Only monitor Cell C4
If Target.Address = Range("$C$2").Address Then​

'Stop Screen Updating
With Application
.ScreenUpdating = False​

End With​

'If Target is Blank then apply Autofilter.
If IsEmpty(Range("c2").Value) = True Then​

Range("b3:g3").AutoFilter
​

'Then apply unhide Worksheet.​

For Each ws In ThisWorkbook.Sheets​

If (ws.Name <> "Template" And ws.Name <> "Definitions") Then​

ws.Visible = xlSheetVisible​

End If​

Next ws​

Else

'If Target is not Blank apply AdvancedFiler
​

Range("$B$3:$G$30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("$c$1:$c$2"), Unique:=False​

For Each ws In Worksheets​

If ws.Name <> "TOC" Or ws.Range("A2") <> Worksheets("TOC").Range("C2").Value Then​

ws.Visible = 0​

If (ws.Name = "TOC" Or ws.Range("A2") = Worksheets("TOC").Range("C2").Value) Then​

ws.Visible = -1​

​

End If​

End If​

Next ws​

End If

​

Worksheets("TOC").Activate​

​

'Start Screen Updating​

With Application​

.ScreenUpdating = True​

End With​

End If
​

End Sub