We're working on getting RefEdit controls running for all VB forms in Office for Mac 2016 and hope to roll them out in an update in early 2016. Once this happens, you'll have the old 'select cells' behavior you're used to in Solver and any other forms that rely on it.
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 WorksheetChange(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 WorksheetActivate procedure, it works fine and doesn't crash Private Sub WorksheetActivate Worksheets('testpage').Range('A1:A8').Formula = '=B1+C1' End Sub But I really need it to work in the WorksheetChange procedure. Has anyone experienced similar crashes when using the WorksheetChange event and can anyone point in the right direction to fix this issue? Note: I have been referring people to this link quite often now so I will make this a one stop post for WorksheetChange.
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 WorksheetChange. 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?. Whenever you are working with WorksheetChange 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.
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 WorksheetChange(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 WorksheetChange(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 WorksheetChange(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 WorksheetChange(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 WorksheetChange(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 WorksheetChange(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.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |