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.
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 ?
Check that the change event is triggering too; place a breakpoint in the first line of the change event code (F9 on the keyboard). If it's not, then check the status of Application. EnableEvents (it should be True).
If you're experiencing high CPU usage, Excel might crash due to insufficient resources. This usually happens if you've added animations to your spreadsheet. To fix it, you should disable hardware graphics acceleration. Besides keeping Excel from freezing or crashing, it will improve the overall performance.
I recommend this when using Worksheet_Change
You do not need the sheet name. In a Sheet Code Module, an unqualified Range reference refers to that sheet. That said, it is clearer to use the Me
qualifier. If you are trying to use another sheet, then qualify the range reference with that sheet.
Whenever you are working with Worksheet_Change
event, always switch Off
events if you are writing data to any cell. This is required so that the code doesn't retrigger the Change event, and go into a possible endless loop
Whenever you are switching off events, use error handling to turn it back on, 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 Me.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 Long
value which can error out in Excel 2007 becuase of increased total cells count.
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, Me.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, Me.Range("A1:A10")) Is Nothing Then MsgBox "one or more Cells in A1:A10 range was changed" '~~> Your code here End If End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With