How to improve this VBA

Question

I have read and tired many things, but i cant stop this Error 9 every time a cell is changed in range "B2:B26" I was hoping the code would ; when a value is added to a cell in range B2:B26i would look in the cell "A" and open the sheet with the name in cell "A". It totally works apart from the error 9 comes up every time. any help woulf be great.

Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Dashboard") 'Name of the primary worksheet
Dim wsName As String
Set KeyCells = Range("B2:B26")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        
For a = 2 To 29
    wsName = sh.Range("A" & a).Value
        'If wsName <> "" Then
        If sh.Range("B" & a).Value <> 0 Then
            ThisWorkbook.Sheets(wsName).Visible = True
        Else
         If sh.Range("B" & a).Value = 0 Then
            ThisWorkbook.Sheets(wsName).Visible = False
        'End If
   End If
   End If
Next a
End If
Application.ScreenUpdating = True
End Sub

Answer 1

(o) Error 9 ("Subscript out of range") happens if you want to access a member of a collection that doesn't exist - in your case you want to show or hide a non-existing sheet. Double-check the content of your data in column A.

(o) You can prevent the runtime error 9 by using error handling. The statement On Error Resume Next is dangerous because it hides all runtime errors, so limit it only to that statement where you expect a possible error (and handle that error by yourself).

(o) As you have your code in the Change-event, you don't have to loop over all possible cells, only the cells that where modified. For this, check the target-parameter. To get the name of the sheet of a modified cell in column B, use the Offset-function to get the name of the sheet from column A.

(o) Also, as you have your code in the Change-event, you don't need to use a worksheet variable. Within the change event, the ActiveSheet is always the sheet that triggered the event.

(o) You can shorten the code that shows or hides the sheets.

Your code could look like that:

Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    
    Dim cell As Range
    For Each cell In Target  ' Loop over all modified cells
        If Not Intersect(Range("B2:B26"), cell) Is Nothing Then
            ' Something in column B was changed.
            Dim wsName As String
            wsName = cell.Offset(0, -1).Value  ' Get sheet name from column A
            If wsName <> "" Then  ' Only if the name column is filled
                On Error Resume Next   ' Begin of manual error handling
                ' Show or hide the sheet
                ThisWorkbook.Sheets(wsName).Visible = (cell.Value <> 0)
                If Err.Number = 9 Then
                    ' Runtime error 9 happened, the sheet does not exist. 
                    MsgBox "Sheet " & wsName & " is not present in this workbook."
                End If
                On Error GoTo 0        ' End of manual error handling
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub