By derek


2012-12-13 13:31:14 8 Comments

I am having a problem with Excel crashing, when I run VBA code on an excel sheet.
I'm trying to add the following formula on worksheet change:

Private Sub Worksheet_Change(ByVal Target As Range)
   Worksheets("testpage").Range("A1:A8").Formula = "=B1+C1"
End Sub

When this code is run i get a message saying "excel has encountered a problem and needs to close" and excel closes.

enter image description here

If I run the code in the Worksheet_Activate() procedure, it works fine and doesn't crash

Private Sub Worksheet_Activate()
   Worksheets("testpage").Range("A1:A8").Formula = "=B1+C1"
End Sub

But I really need it to work in the Worksheet_Change() procedure.

Has anyone experienced similar crashes when using the Worksheet_Change() event and can anyone point in the right direction to fix this issue ?

3 comments

@Mario Palumbo 2018-08-17 17:52:54

Also this solution is good:

Option Explicit
Private Busy As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Busy Then
        Busy = True
        Range("A1:A8").Formula = "=B1+C1"
        Busy = False
    End If
End Sub

@Siddharth Rout 2012-12-13 14:11:54

Note: I have been referring people to this link quite often now so I will make this a one stop post for Worksheet_Change. Every now and then, when I get the time, I will add new content to this so people can benefit for it.


I always recommend this when using Worksheet_Change

  1. You do not need the sheet name. It is understood that the code is to be run on current sheet UNLESS you are trying to use another sheet as a reference. Is "testpage" the Activesheet name or is it a different sheet?

  2. Whenever you are working with Worksheet_Change event. Always switch Off events if you are writing data to the cell. This is required so that the code doesn't go into a possible endless loop

  3. Whenever you are switching off events, use error handling else if you get an error, the code will not run the next time.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    Range("A1:A8").Formula = "=B1+C1"

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Few other things that you may want to know when working with this event.

If you want to ensure that the code doesn't run when more than one cell is changed then add a small check

Private Sub Worksheet_Change(ByVal Target As Range)
    '~~> For Excel 2003
    If Target.Cells.Count > 1 Then Exit Sub

    '
    '~~> Rest of code
    '
End Sub

The CountLarge was introduced in Excel 2007 onward because Target.Cells.Count returns an Integer value which errors out in Excel 2007 becuase of increased rows/columns. Target.Cells.CountLarge returns a Long value.

Private Sub Worksheet_Change(ByVal Target As Range)
    '~~> For Excel 2007
    If Target.Cells.CountLarge > 1 Then Exit Sub
    '
    '~~> Rest of code
    '
End Sub

To work with all the cells that were changed use this code

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

    For Each aCell In Target.Cells
        With aCell
            '~~> Do Something
        End With
    Next
End Sub

To detect change in a particular cell, use Intersect. For example, if a change happens in Cell A1, then the below code will fire

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        MsgBox "Cell A1 was changed"
        '~~> Your code here
    End If
End Sub

To detect change in a particular set of range, use Intersect again. For example, if a change happens in range A1:A10, then the below code will fire

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        MsgBox "Cell in A1:A10 range was changed"
        '~~> Your code here
    End If
End Sub

@derek 2012-12-13 14:18:47

thanks Siddharth when i removed the sheet name it worked fine without any crashes

@Siddharth Rout 2012-12-13 14:19:37

Gr8! I hope you switched off/on the events as well as suggested above?

@derek 2012-12-13 14:21:23

No will i have to do that aswell ?

@Siddharth Rout 2012-12-13 14:22:24

It is recommended that you do it. See above code example...

@derek 2012-12-13 14:26:19

Will do , Thanks for your help

@Doug Glancy 2012-12-13 14:36:09

+1. One other good practice is to further limit the area whose change triggers the code by surrounding with something like: If Not Intersect(Target, Range("D1:D8")) Is Nothing Then ... End If. In this example, your code would only run if a cell in this range was changed.

@Erik Eidt 2012-12-18 19:53:08

@Siddharth, @Doug, why use Range() inside the Worksheet_Change function, instead of Target.Parent.Range(), which I would argue is more correct and safer? Range() refers to the active sheet, which isn't necessarily the one that is being changed (e.g. if VBA code causes the change to this worksheet with another worksheet active).

@Doug Glancy 2012-12-18 20:39:41

@ErikEidt, I generally use Me.Range inside the event. But my testing confirms what Siddharth says in his answer, i.e., Range inside a Worksheet's Change event will refer to a range in that sheet, regardless of which sheet is active.

@Erik Eidt 2012-12-21 23:22:32

@Doug, ok thanks for that info. I think you're right, even though according to msdn.micosoft.com, When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn’t a worksheet, the property fails). And we know that ActiveSheet doesn't necessarily change inside the event function to the changing sheet. Guess the official docs are somewhat in error on this one.

@Tim Williams 2016-04-04 16:23:54

@SiddharthRout - I think it's worth expanding on the use of Intersect() when checking Target against a desired range. I see many questions on SO where the issue was that the OP would just check Intersect was not Nothing, then go on to process the full Target range (which may well include cells outside of the desired range...)

@Siddharth Rout 2016-04-04 16:31:45

Good Idea @TimWilliams! Will do it as soon as I can.

@stenci 2013-01-08 06:28:43

Excel was crashing, not the VBA function.
The events were not disabled and the call stack was filled by an infinite loop of OnChange events.
A little advice that helps finding this type of errors: set a breakpoint on the first line of the event, then execute it step by step pressing F8.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] How to merge two subs with Private Sub Worksheet_Change on sheet

  • 2019-04-17 06:58:42
  • yiannis
  • 46 View
  • 1 Score
  • 1 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Crash when deleting cell values Worksheet_Change event

  • 2019-01-14 21:10:13
  • Aas
  • 41 View
  • 1 Score
  • 1 Answer
  • Tags:   excel vba

1 Answered Questions

1 Answered Questions

[SOLVED] Private Sub Worksheet_Calculate () crashing excel

1 Answered Questions

[SOLVED] How to run Multiple Private Sub Worksheet_Change(ByVal Target As Range)?

  • 2017-12-27 05:59:57
  • Father Goose
  • 1209 View
  • 0 Score
  • 1 Answer
  • Tags:   excel

2 Answered Questions

1 Answered Questions

[SOLVED] How to run sub procedure

  • 2017-02-16 12:39:03
  • user7561012
  • 303 View
  • -1 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

Excel crashing on Worksheet_Change, but NOT on Worksheet_SelectionChange

1 Answered Questions

1 Answered Questions

[SOLVED] open mssql stored procedure from excel 2007 VBA and get data to excel

Sponsored Content