#VBA - Private Sub Worksheet_Change Error: Method 'Hidden' of Object 'Range' Field

Spent over 3+ hours trying to trouble shoot this. Any help would be appreciated. Solutions I've tried at the bottom.

PROBLEM:

I'm trying to update the code so that if anyone selects "NA" in cell E58, (there are 3 drop-down options on this cell: Yes/No/NA) it updates the value of cell E60 and E65 as being NA as well, without disrupting current actions. Every solution I've tried results in the code cycling back to the top and failing on the 6th line from the top and/or crashing the file:

   Rows("9").EntireRow.Hidden = True

Error message is: Run-Time Error, Method "Hidden" of Object 'Range' field

Code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)   

If Range("E8").Value = "No" Then
    Rows("9").EntireRow.Hidden = True
ElseIf Range("E8").Value = "Yes" Then
    Rows("9").EntireRow.Hidden = False
    ElseIf Range("E8").Value = "" Then
    Rows("9").EntireRow.Hidden = True

End If

 If Range("E10").Value = "No" Then
    Rows("11").EntireRow.Hidden = True
ElseIf Range("E10").Value = "No" Then
    Rows("11").EntireRow.Hidden = False
ElseIf Range("E10").Value = "Yes" Then
    Rows("11").EntireRow.Hidden = True
     ElseIf Range("E10").Value = "" Then
    Rows("11").EntireRow.Hidden = True

End If

If Range("E58").Value = "Yes" Then
    Rows("59").EntireRow.Hidden = True
     
ElseIf Range("E58").Value = "NA" Then
    Rows("59").EntireRow.Hidden = True
    Range("E60").Value = "NA"
ElseIf Range("E58").Value = "No" Then
    Rows("59").EntireRow.Hidden = False
 ElseIf Range("E58").Value = "" Then
    Rows("59").EntireRow.Hidden = True

End If


 
 If Range("E60").Value = "No" Then
    Rows("61").EntireRow.Hidden = True
    Rows("62").EntireRow.Hidden = False
    Rows("63").EntireRow.Hidden = True
ElseIf Range("E60").Value = "NA" Then
    Rows("61:62").EntireRow.Hidden = True
ElseIf Range("E60").Value = "Yes" Then
    Rows("62").EntireRow.Hidden = False
    Rows("63").EntireRow.Hidden = False
    Rows("61").EntireRow.Hidden = True
ElseIf Range("E60").Value = "" Then
    Rows("61:63").EntireRow.Hidden = True
End If



If Range("E63").Value = "No" Then
    Rows("64").EntireRow.Hidden = False
ElseIf Range("E63").Value = "N/A" Then
    Rows("64").EntireRow.Hidden = True
ElseIf Range("E63").Value = "Yes" Then
    Rows("64").EntireRow.Hidden = True
ElseIf Range("E63").Value = "Partial" Then
    Rows("64").EntireRow.Hidden = False
ElseIf Range("E63").Value = "" Then
    Rows("64").EntireRow.Hidden = True
End If
 
 
 If Range("E65").Value = "False" Then
    Rows("66").EntireRow.Hidden = True
    Rows("67").EntireRow.Hidden = True
ElseIf Range("E65").Value = "NA" Then
    Rows("66:67").EntireRow.Hidden = True
ElseIf Range("E65").Value = "Yes" Then
    Rows("66").EntireRow.Hidden = False
    Rows("67").EntireRow.Hidden = False
ElseIf Range("E65").Value = "" Then
    Rows("66:67").EntireRow.Hidden = True
End If

Note: there are similar commands for rows not mentioned but this action is only required here.

SOLUTIONS TRIED:

Tried adding a line to the ELSEIF statement

  ElseIf Range("E58").Value = "NA" Then
  Range("E60").Value = "NA"

While this does spit out the NA in E60, the code cycles back to the top and fails on line 6 again.

Tried adding a public sub that would do the same as above with a call for this public sub placed in the same place as the Range("E60").Value = "NA" statement but it fails at the same spot and/or crashes the excel.
Tried to experiment with Application.EnableEvents = True/False but this causes the ELSEIF statement ( ElseIf Range("E58").Value = "" Then Rows("59").EntireRow.Hidden = True) to not function correctly.

Help would be very much appreciated.

Thank you.

Solution

You have to add Application.EnableEvents = False right after Private Sub Worksheet_Change(ByVal Target As Range) and Application.EnableEvents = True before End Sub.

Since you are trying to change cell values on the same sheet, the Worksheet_Change event will trigger itself when you change cell values on the same sheet and thereby lead to an infinite loop.

The modified code will look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Range("E8").Value = "No" Then
        Rows("9").EntireRow.Hidden = True
    ElseIf Range("E8").Value = "Yes" Then
        Rows("9").EntireRow.Hidden = False
    ElseIf Range("E8").Value = "" Then
        Rows("9").EntireRow.Hidden = True
    End If

    If Range("E10").Value = "No" Then
        Rows("11").EntireRow.Hidden = True
    ElseIf Range("E10").Value = "No" Then
        Rows("11").EntireRow.Hidden = False
    ElseIf Range("E10").Value = "Yes" Then
        Rows("11").EntireRow.Hidden = True
    ElseIf Range("E10").Value = "" Then
        Rows("11").EntireRow.Hidden = True
    End If

    If Range("E58").Value = "Yes" Then
        Rows("59").EntireRow.Hidden = True
    ElseIf Range("E58").Value = "NA" Then
        Rows("59").EntireRow.Hidden = True
        Range("E60").Value = "NA"
    ElseIf Range("E58").Value = "No" Then
        Rows("59").EntireRow.Hidden = False
    ElseIf Range("E58").Value = "" Then
        Rows("59").EntireRow.Hidden = True
    End If
 
    If Range("E60").Value = "No" Then
        Rows("61").EntireRow.Hidden = True
        Rows("62").EntireRow.Hidden = False
        Rows("63").EntireRow.Hidden = True
    ElseIf Range("E60").Value = "NA" Then
        Rows("61:62").EntireRow.Hidden = True
    ElseIf Range("E60").Value = "Yes" Then
        Rows("62").EntireRow.Hidden = False
        Rows("63").EntireRow.Hidden = False
        Rows("61").EntireRow.Hidden = True
    ElseIf Range("E60").Value = "" Then
        Rows("61:63").EntireRow.Hidden = True
    End If

    If Range("E63").Value = "No" Then
        Rows("64").EntireRow.Hidden = False
    ElseIf Range("E63").Value = "N/A" Then
        Rows("64").EntireRow.Hidden = True
    ElseIf Range("E63").Value = "Yes" Then
        Rows("64").EntireRow.Hidden = True
    ElseIf Range("E63").Value = "Partial" Then
        Rows("64").EntireRow.Hidden = False
    ElseIf Range("E63").Value = "" Then
        Rows("64").EntireRow.Hidden = True
    End If
 
    If Range("E65").Value = "False" Then
        Rows("66").EntireRow.Hidden = True
        Rows("67").EntireRow.Hidden = True
    ElseIf Range("E65").Value = "NA" Then
        Rows("66:67").EntireRow.Hidden = True
    ElseIf Range("E65").Value = "Yes" Then
        Rows("66").EntireRow.Hidden = False
        Rows("67").EntireRow.Hidden = False
    ElseIf Range("E65").Value = "" Then
        Rows("66:67").EntireRow.Hidden = True
    End If
    
    Application.EnableEvents = True

End Sub

Note: I assume that you had some more elements in your code. You write

I'm trying to update the code so that if anyone selects "NA" in cell E58, (there are 3 drop-down options on this cell: Yes/No/NA) it updates the value of cell E60 and E65 as being NA as well,...

but your code does not update those cells. Just hiding/unhiding rows or columns does not trigger a Worksheet_Change event and will therefor not result in an infinite loop.