Private Sub for hiding worksheets based on cell value "View" / "Hidden"

Question

This is my first time dealing with Private Subs and I am not sure if they work in the same way (same syntax, variables & etc.) Pretty new to VBA in general.

I have a list of 365 days in a year on Sheet1 (LIST) I created 365 worksheets that are numbered 1 - 365

On the main page (LIST) there is a drop down selection box with "View" or "Hide" as an option. I am trying to create a private sub that will hide or show sheets with matching names based on the cell value "View"/"Hide"

EX:

(LIST)

1 View

2 View

3 Hide

4 Hide

5 View

I would like only sheets named "1", "2", and "5" to be visible

CODE I TRIED (last iteration of many attempts)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim sheetname As String
Dim rng As Range, cell As Range
Dim ws As Worksheet

`Set rng = Range("F2:F366")`

    For Each cell In rng
    
        sheetname = Cells(cell.row, "A").Value
        ws = Worksheets("sheetname")        
     
            If cell.Value = "View" Then
                ws.Visible = True
            ElseIf cell.Value = "Hide" Then
                
                ws.Visible = False
            End If

    Next cell

End Sub

At first, the code was running with errors after changing selections on main page. Now, it doesn't seem to be running at all.

It is the worksheet/selectionChange code attached to the LIST worksheet.

Can anyone help point me in the right direction?

Answer 1

The Worksheet_SelectionChange event triggers when users select cell(s), which may not be ideal since no actual changes (desired sheet status) to the cells occur. The Worksheet_Change event would be more suitable for your situation.

Microsoft documentation:

Worksheet.SelectionChange event (Excel)

Worksheet.Change event (Excel)

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim sheetname As String
    Dim rng As Range, cell As Range
    Dim ws As Worksheet
    ' sheet status range
    Set rng = Me.Range("F2:F366")
    ' changed cell(s) in rng
    If Not Application.Intersect(rng, Target) Is Nothing Then
        ' loop through cell
        For Each cell In Application.Intersect(rng, Target).Cells
            ' Trim convert cell content to string and remove non-printable chars
            ' If cell content is a number (ie. 3), Sheets(3) might be different with Sheets("3")
            sheetname = Trim(Me.Cells(cell.Row, "A").Value)
            ' get sheet object
            set ws = Nothing
            On Error Resume Next
            Set ws = Worksheets(sheetname)
            On Error GoTo 0
            ' sheet exists
            If Not ws Is Nothing Then
                ' change visibility
                If cell.Value = "View" Then
                    ws.Visible = xlSheetVisible 
                ElseIf cell.Value = "Hide" Then
                    ws.Visible = xlSheetHidden 
                End If
            End If
        Next cell
    End If
End Sub

• Run below script to initialize sheet status

Private Sub SheetStatus()    
    Dim sheetname As String
    Dim rng As Range, cell As Range
    Dim ws As Worksheet
    ' sheet status range
    Set rng = Sheets("List").Range("F2:F366")
    ' loop through cell
    For Each cell In rng.Cells
        sheetname = Trim(Sheets("List").Cells(cell.Row, "A").Value)
        ' get sheet object
        set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(sheetname)
        On Error GoTo 0
        ' sheet exists
        If Not ws Is Nothing Then
            ' change visibility
            If cell.Value = "View" Then
                ws.Visible = xlSheetVisible 
            ElseIf cell.Value = "Hide" Then
                ws.Visible = xlSheetHidden 
            End If
        End If
    Next cell
End Sub