Excel VBA

Worksheet Change Event, Excel VBA

User Rating:  / 15
PoorBest 
Details

 

Worksheet Change Event in VBA and Preventing Event Loops

 

Related Links: 

Excel VBA Events, Event Handlers, Trigger a VBA Macro.

Worksheet Selection Change Event, Excel VBA.

 

 

 

Worksheet_Change Event:

 

You can auto run a VBA code, when content of a worksheet cell changes, with the Worksheet_Change event. The change event occurs when cells on the worksheet are changed either by the user, or by any VBA application or by an external link, but not when a cell changes due to recalculation as a result from formula or due to format change. For changes made by calculation, use Worksheet_Calculate event.

 

Worksheet change procedure is installed with the worksheet, ie. it must be placed in the code module of the appropriate Sheet object. To create a worksheet change event: use the Visual Basic Editor -> in the Project Explorer, double click on the appropriate sheet (under 'Microsoft Excel Objects' which is under the VBAProject/name of your workbook) -> in the Code window, select "Worksheet" from the left-side "General" drop-down menu and then select "Change" from the right-side "Declarations" drop-down menu. You will get a procedure "shell" in the code window as follows:

 

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

 

 

Target is a parameter of data type Range (ie. Target is a Range Object). It refers to the changed Range and can consist of one or multiple cells. If Target is in the defined Range, and its value or content changes, it will trigger the vba procedure. If Target is not in the defined Range, nothing will happen in the worksheet. In this manner, you can limit the events to a particular range for both the Change and SelectionChange events. This can be done in multiple ways:

 

Using Target Address. Trigger the procedure, if a single cell (A5) value is changed:

If Target.Address = "$A$5" Then MsgBox "Success"

If Target.Address = Range("$A$5").Address Then MsgBox "Success"

If Target.Address = Range("A5").Address Then MsgBox "Success"

 

Using Target Address. If cell (A1) or cell (A3) value is changed:

If Target.Address = "$A$1" Or Target.Address = "$A$3" Then MsgBox "Success"

 

Using Target Address. If any cell(s) value other than that of cell (A1) is changed:

If Target.Address <> "$A$1" Then MsgBox "Success"

 

The following use of Target.Address is not correct, and the code will not run:

If Target.Address = "$a$5" Then MsgBox "Success"

If Target.Address = "A1" Then MsgBox "Success"

If Target.Address = "$A$1:$A$10" Then MsgBox "Success"

If Target.Address = Range("$A$1:$A$10") Then MsgBox "Success"

 

Note: Target.Address should be an absolute reference [unless used as Range("A5").Address - see above] and in Caps. Use this to run code when content of a single cell is changed or when any cell(s) other than a specific cell is changed.

 

 

Trigger the procedure, if any cell in a column(s) is changed, say for any change in a cell in column B or column C:

If Target.Column = 2 Or Target.Column = 3 Then MsgBox "Success"

 

 

Intersect method for a single cell. If Target intersects with the defined Range of A1 ie. if cell (A1) value is changed, the code is triggerred:

If Not Application.Intersect(Target, Range("A1")) Is Nothing Then MsgBox "Success"

 

Trigger the procedure, if at least one cell of Target is A1,B2,C3:

If Not Application.Intersect(Target, Range("A1,B2,C3")) Is Nothing Then MsgBox "Success"

 

At least one cell of Target is within the range C5:D25:

If Not Application.Intersect(Target, Me.Range("C5:D25")) Is Nothing Then MsgBox "Success"

 

 

If you want the code to run when only a single cell in Range("C1:C10") is changed and do nothing if multiple cells are changed:

 

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("C1:C10")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
MsgBox "Success"
End If


End Sub

 

 

 

Preventing Event Loops with Application.EnableEvents = False

 

Example of a recursive loop code:

 

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Range("A5").Value = Range("A5").Value + 1
End If


End Sub

 

Recursive Event Loop:

 

If, at each runtime, the worksheet_change event changes the content of a cell which itself is part of the Target Range (ie. which triggers the change event), it will result in reprocessing the change event repeatedly.  Recursion is the process of repeating in a similar way viz. when the procedure calls itself. Refer to above example of a recursive loop code, if any cell content in Range("A1:A10") is changed by the user, the cell A5 value will change and increment by 1; this will again trigger the change event [because of change in value of cell A5 which is in the Target Range("A1:A10")] and will in turn change the cell A5 value by incrementing it by 1; and then this change in cell A5 will again trigger the change event and change the cell A5 value by incrementing it by 1; and so on. This will result in a recursive loop which might result in a 'Out Of Stack Space' untrappable error, or depending on the Excel setting, the loop might terminate at a threshold limit of say 100. To prevent this, enter the following at the beginning of the code: Application.EnableEvents = False. This means that any change made by the VBA code will not trigger any event and will not enable restarting the worksheet_change event. EnableEvents is not automatically changed back to True, this should be specifically done in your code, by adding the following line at the end of the code: Application.EnableEvents = True. Meanwhile, if during runtime, your code encounters an error, you will need an ErrorHandler (to change EnableEvents back to True) because events have been disabled in the beginning of the code. This can be done as follows. 

 

ErrorHandler, Example 1:

 

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ErrorHandler  'Enable error-handling routine for any run-time error
Application.EnableEvents = False

If Target.Address = "$A$1" Or Target.Address = "$B$1" Then Exit Sub
 
  If Target.Column = 1 Or Target.Column = 2 Then
     If IsNumeric(Target) Then
       Range("B2").Value = Range("B2").Value + 1
     End If
  End If

Application.EnableEvents = True

ErrorHandler:
  Application.EnableEvents = True  'EnableEvents is changed back to True on any error

 

End Sub

 

 

ErrorHandler, Example 2:

 

Private Sub Worksheet_Change(ByVal Target As Range)


On Error Resume Next  'skip all run-time errors
Application.EnableEvents = False

If Target.Address = "$A$1" Or Target.Address = "$B$1" Then Exit Sub
 
  If Target.Column = 1 Or Target.Column = 2 Then
     If IsNumeric(Target) Then
     'If WorksheetFunction.mod(1 / 0) = 2 Then
       Range("B2").Value = Range("B2").Value + 1
     End If
  End If

Application.EnableEvents = True

On Error GoTo 0  'Turn off error trapping and re-allow run time errors

 

End Sub

 

 

 

On Error Statements explained:


On Error Resume Next: Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred, and execution continues from that point.

 

The On Error GoTo 0 statement turns off error trapping.  It disables enabled error handler in the current procedure and resets it to Nothing.

 

On Error GoTo Line: Enables the error-handling routine that starts at the specified Line. The On Error GoTo statement traps all errors, regardless of the exception class.

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.