VBA Crashes Excel with Nested If Statements and Intersect

[truebluewoman]

I am trying to write VBA code that will change the values of specific rows based on what is entered within 3 columns of information. What I've written works, but then crashes the file every time. I don't know what I am doing wrong.

1. I have a named range of a group of columns and rows, "VAR_OPTIONS". The cells are '3-Other Personnel Exp'!$P$24:$R$30
2. I have drop-downs available for 2 of these columns (P and R). Column R is a number that someone has to type in.
3. I need that when someone changes any value within that range that it will perform a series of changes to the cells based on information entered within the same row, but in other columns.
3. Below is the code I have written. Every time it runs, it will perform the tasks, but then crashes.

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

If Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Annual" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = True
   Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
 

ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Monthly" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
    
    Sheets("3-Other Personnel Exp").Range("Q" & Target.Row & ":R" & Target.Row).Locked = True
    Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
    Sheets("3-Other Personnel Exp").Range("R" & Target.Row).Value = "Monthly"


ElseIf Sheets("3-Other Personnel Exp").Range("K" & Target.Row).Value = "Variable" And Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Budgeted" Then
    
    Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Locked = True
    Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 1
    Sheets("3-Other Personnel Exp").Range("R" & Target.Row).Locked = False
 
ElseIf Sheets("3-Other Personnel Exp").Range("P" & Target.Row).Value = "Not Budgeted" Then
    
    Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Locked = True
    Sheets("3-Other Personnel Exp").Range("Q" & Target.Row).Value = 0
        
Else
    Exit Sub
 
End If
     
End Sub

 

[mikerickson]

The first thing that I notice is that your Change event is triggering other change events.
You might want to wrap your whole routine in Application.EnableEvents = False/True

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Intersect(Target, Range("VAR_OPTIONS")) Is Nothing Then Exit Sub

Application.EnableEvents = False

'....

Application.EnableEvents = True
End Sub

 

[truebluewoman]

Oh my goodness. That actually did it. I'm still learning VBA, so these are the kinds of things that I just do not know enough to look for and totally miss. Thank you SO much!!! You have no idea what having this fixed means to me. Have a wonderful evening.