4

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:

  1. 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.

  1. 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.

  2. 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.

VBAWARD
  • 71
  • 1
  • 12
  • I have just tried that and if I add `Application.EnableEvents = False` right after `Private Sub Worksheet_Change(ByVal Target As Range)` and `Application.EnableEvents = True` before `End Sub`, it works perfectly fine. What happens when you include those two statements? There is always a chance that you will start an infinite loop when using a `Worksheet_Change` event that manipulates cell values in the same sheet. – Michael Wycisk Aug 10 '20 at 15:14
  • If you're changing a value in the sheet during the update event you absolutely must use `Application.EnableEvents = false` to avoid an infinite loop. Is there any code you've not posted here that does change a value on the sheet? – Absinthe Aug 10 '20 at 15:14
  • @MichaelWycisk Ha, beat me by 4 seconds ;) – Absinthe Aug 10 '20 at 15:14
  • @Absinthe Yes :-). But I agree with you that VBAAWARD did not post all the code, the code posted above does not change any cell values (but the question states that cell `E60` and `E65` should be updated). From what I see, hiding/showing rows alone should not trigger a `Worksheet_Change` event. – Michael Wycisk Aug 10 '20 at 15:18
  • @MichaelWycisk i tried this before but i must have done something wrong - it worked. it seems that was it. i added the application.enableevents line to the top and bottom (false/true) and added in 2 lines of code at the *ELSEIF Range("E58").value = NA then* point and it worked! thanks so much! – VBAWARD Aug 10 '20 at 15:25
  • Have you seen [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640)? – Siddharth Rout Aug 13 '20 at 06:20

2 Answers2

2

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.

Michael Wycisk
  • 1,590
  • 10
  • 24
1

Run the code you want only when cells of your choice are changed, not any cell in the worksheet.

For example :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been 
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."

    End If
End Sub

For this code only shows the message for cells out from Range("A1:C10"). In your case, you should run some specific logic when the change occurs in the E58 and exclude the E60 and E65 on the Worksheet_Change event.

Example from here. Give it a read. This should achieve what you want with little effort.

rustyBucketBay
  • 4,320
  • 3
  • 17
  • 47
  • thanks for sharing your insight! quick question since the above is taken from the microsoft page directly: how exactly do i apply this to the code i have? i dont need the msgbox. so do i add the IF NOT statement to EACH section of the IF/ELSEIF/END IF statement? not quite sure how to apply it. thanks in advance. – VBAWARD Aug 10 '20 at 13:08
  • Tried to apply this to my code and it crashed the file again. any insight on how to actually apply this would be very much appreciated. thanks. – VBAWARD Aug 10 '20 at 13:26