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

termeric

Active Member
Joined
Jun 21, 2005
Messages
280
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?




VBA Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Rich (BB code):
            Application.EnableEvents = False
            Range("LCCFirstYr").Value = "N/A"
            Application.EnableEvents = True
 
Upvote 0
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:
Rich (BB code):
            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?
 
Upvote 0
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.
VBA Code:
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top