Worksheet Selection Change Event in Excel VBA and Preventing Event Loops

 

Related Links:

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

Worksheet Change Event in VBA and Preventing Event Loops.

 

————————————————————————   

Contents:

Worksheet_SelectionChange Event

Preventing Event Loops with Application.EnableEvents = False

————————————————————————   

 

Worksheet_SelectionChange Event:

 

You can auto run a VBA code, each time that you make a new selection on the worksheet, with the Worksheet_SelectionChange event. The selection change event occurs when the selection changes on a worksheet, either by the user or by any VBA application. The Worksheet_Change event fires when content in a cell changes, while the Worksheet_SelectionChange event fires whenever a new cell is selected.

 

 

 

Worksheet SelectionChange procedure is installed with the worksheet, ie. it must be placed in the code module of the appropriate Sheet object. To create a worksheet SelectionChange 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 “SelectionChange” from the right-side “Declarations” drop-down menu. You will get a procedure “shell” in the code window as follows:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

 

 

Target is a parameter of data type Range (ie. Target is a Range Object). It refers to the SelectionChange Range and can consist of one or multiple cells. If Target is in the defined Range, and when the selection changes within this Range, 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. See the Worksheet Change Event in VBA and Preventing Event Loops page for details on using the Target parameter, Error Handlers and to Enable or Disable Events in a code.

 

 

 

Sample Codes for Worksheet_SelectionChange Event:

 

Background color of a cell(s) changes to blue each time a new selection is made, only if a single and empty new cell is selected:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count = 1 And IsEmpty(Target) Then Target.Interior.Color = vbBlue

End Sub

 

 

Increments cell B2 whenever a new cell is selected in column 1 or column 2 (except selection of cells A1 and B1), and if the selected cell is a numeric:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

‘on selecting cell A1 or B1, exit sub
If Target.Address = “$A$1” Or Target.Address = “$B$1” Then Exit Sub

 

‘if any cell in column 1 or 2 is selected

If Target.Column = 1 Or Target.Column = 2 Then

‘if cell value is numeric    

If IsNumeric(Target) Then

‘increment cell B2 value by 1      

Range(“B2”).Value = Range(“B2”).Value + 1

End If

End If

End Sub

 

 

 

Preventing Event Loops with Application.EnableEvents = False

 

 

Recursive Event Loops (though most common in Worksheet_Change events) might happen in Worksheet_SelectionChange events, as in the following example of a recursive loop code:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Offset(1, 1).Select

End Sub

 

 

Recursive Event Loop:

 

If, at each runtime, the Worksheet_SelectionChange event changes the selection of a cell which itself is part of the Target Range (ie. which triggers the SelectionChange event), it will result in reprocessing the SelectionChange 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 selection in the worksheet is changed by the user, another cell [Offset(1,1)] is selected, which again triggers the SelectionChange event and which will in turn select another cell, 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_SelectionChange 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. 

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

‘on the occurrence of an error, say selecting cell A1 will cause an error, procedure flow is directed to the error-handling routine (ie. ErrHandler) which handles the error
On Error GoTo ErrHandler

 

‘to prevent recursion, so that any change made by code will not trigger an event to restart the Worksheet_SelectionChange event
Application.EnableEvents = False

‘on selection of a cell (Target), this procedure selects one cell above the left of the target cell – offsets by 1 row (up) & 1 column (left)
Target.Offset(-1, -1).Select

‘EnableEvents is not automatically changed back to True & hence this needs to be done specifically at the end of the code before exit.

‘because an exit statement (ex. Exit Sub) is not placed above, the error-handling routine will also execute when there is no error.

ErrHandler:

Application.EnableEvents = True

End Sub