Lots of Errors with Worksheet Change( ByVal Target as Range)

[termeric]

I'm having a problem where excel is crashing and restarting when i'm checking my spreadsheet for a value change and i'm getting a few different errors including out of stack space. I'm pretty sure i have isolated it to the IF statement for Row 35 and LCCModel. Is this happening because i am changing a value as part of my code, which then re-triggers the subroutine forever? is there a better way to structure my code when i need to check individual cells for changes and then hide / show / adjust based on the input?

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("B8:B90")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
   
   
        'Row 15
        'Hide row if MultiYear <> Yes, Not last year of guarantee
        If Range("multiYear").Value = "Yes, not last year of guarantee" Then
            Range("rEndDate").EntireRow.Hidden = True
        Else
            Range("rEndDate").EntireRow.Hidden = False
        End If
       
        'Row 23
        'Hide if ContractType <> New Business or R Vendor <> Third Party
        If Range("ContractType").Value = "New Business" And Range("RVendor").Value = "Third Party" Then
            Range("rThird").EntireRow.Hidden = False
        Else
            Range("rThird").EntireRow.Hidden = True
        End If
       
        'row 31
        'Set to No and Hide row if CMModel =  One Flex,  One Choice,  One Advisor or  One Advocate
        If Range("CMModel").Value = " One Flex" Or Range("CMModel").Value = " One Choice" Or Range("CMModel").Value = " One Advisor" Or Range("CMModel").Value = " One Advocate" Then
            Range("XQuerey").Value = "No"
            Range("rXQuerey").EntireRow.Hidden = True
        Else
            Range("rXQuerey").EntireRow.Hidden = False
        End If
   
        'row 32
        'Set to N/A and Hide row if CMModel =  One Essentials,  One Advisor or  One Advocate
        If Range("CMModel").Value = " One Essentials" Or Range("CMModel").Value = " One Advisor" Or Range("CMModel").Value = " One Advocate" Then
            Range("Enhance").Value = "N/A"
            Range("rEnhance").EntireRow.Hidden = True
        Else
            Range("rEnhance").EntireRow.Hidden = False
        End If
   
        '************ERROR HAPPENING HERE********************
        'row 35
        'Set to N/A and Hide row if LCCModel = Not included
        If Range("LCCModel").Value = "Not Included" Then
            Range("LCCFirstYr").Value = "N/A"
            Range("rLCCFirstYr").EntireRow.Hidden = True
        Else
            Range("rLCCFirstYr").EntireRow.Hidden = False
        End If
   
   
        'col F
        'If = Preferred (Simplified/Short Version), Hide all acceptable metrics (column F)
        If Range("IncludedMetrics").Value = "Preferred (Simplified/Short Version)" Then
            Range("CPA").EntireColumn.Hidden = True
        Else
            Range("CPA").EntireColumn.Hidden = False
        End If
   
   
   
   
   
   
    End If
End Sub

 

[Joe4]

Whenever you have a Worksheet_Change event procedure (which fires when a cell is updated) changing other cells, the best thing to do is temporarily disable events long enough to make that change, so it doesn't call itself and get caught in a loop.

So, you can do that like this here:

            Application.EnableEvents = False
            Range("LCCFirstYr").Value = "N/A"
            Application.EnableEvents = True

 

[termeric]

Whenever you have a Worksheet_Change event procedure (which fires when a cell is updated) changing other cells, the best thing to do is temporarily disable events long enough to make that change, so it doesn't call itself and get caught in a loop.

So, you can do that like this here:

            Application.EnableEvents = False
            Range("LCCFirstYr").Value = "N/A"
            Application.EnableEvents = True

thanks, is there any reason why i wouldnt want to do this at the beginning and end so it applies to everything? would that stop all the changes from happening?

 

[Joe4]

thanks, is there any reason why i wouldnt want to do this at the beginning and end so it applies to everything? would that stop all the changes from happening?

Nope, none at all.

Just keep in mind the following:
If you have any exits in the middle of your code (i.e. "Exit Sub" lines), or if you have an error in the middle of the code, you could end up in a state where you turned off events, but never turned them back on (because it never got to that line at the end).

If you use these lines of code, and somewhere along the way of your testing you find that "the automated code isn't running automatically" anymore, then that is probably what happened.

If that happens, there are two ways to fix it:
1. Close out of Excel and re-open it.
- or -
2. Run a little procedure manually to turn events back on, i.e.

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[johnnyL]

Immediate window is another option

To check status of enable events:
?application.enableevents

To turn enableevents back on:
?EnableEvents=True

To turn enableevents off:
?EnableEvents=False