ByVal Sub to Auto Hide sheets in a range

[crburke92]

Hey all,

Trying to build a form to easily clean up the amount of worksheets displayed based on a selection. Currently I have a Checkbox that has a cell reference of C2. C4:C10 will have worksheet names in them, and the adjacent cell D4:D10 has =IF(C2=TRUE,"Y","N"). When I select the checkbox, I want all sheets in the C column to be visible, and when deselected they will hide. I'll have multiple columns running similar code based off what sheets I want to see. My current code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim SheetRef As Range
Dim TargetSheet As Worksheet
    
    If Range("C2") = "True" Then
        For Each SheetRef In Range("C4:C10")
            Set TargetSheet = ThisWorkbook.Worksheets(SheetRef.Offset(0, 1).Value)
            TargetSheet.Visible = (UCase(SheetRef.Value) = Y)
        Else
            For Each SheetRef In Range("C4:C10")
            Set TargetSheet = ThisWorkbook.Worksheets(SheetRef.Offset(0, 1).Value)
            TargetSheet.Hidden = (UCase(SheetRef.Value) = N)
    End If
    
End Sub

I have C4:C10 as Rly1:Rly10, and have sheets made with those names as well. Any help would be great as to why it's not updating!

 

[Fluff]

Change events are not triggered by formulae, as the cell has not changed.
Why not just assign a macro to your checkbox?

 

[crburke92]

Change events are not triggered by formulae, as the cell has not changed.
Why not just assign a macro to your checkbox?

Because that would have been to easy Fluffy! Also didn't know that formulas did not trigger.

It works now....

Thanks as always!

 

[Fluff]

You're welcome & thanks for the feedback.