Excel VBA Tutorial Part 9 - Excel Events

The term 'Excel Events' refers to specific user actions within Excel. For example, if the user selects a Worksheet, enters data into a cell, or saves a Workbook, these actions are all Excel events.

Events are linked to Excel Worksheets, Charts, Workbooks, or to the Excel Application itself. Their purpose is to enable the programmer to create vba code to be executed automatically at the time of an event.

For example, if you wanted to run a macro every time a user selected any Worksheet in the Workbook, this could be done by writing vba code that is linked to the Workbook event "SheetActivate". Alternatively, if you wanted to run a macro every time a specific Worksheet (eg. "Sheet1") was selected, you would link your code to the Worksheet event "Activate" for Sheet1.

The vba code linked to Excel events should be housed in the individual Sheet or Workbook objects in the VBA Editor (accessed by pressing ALT-F11). This is shown in the image below:

Excel Objects in the Excel VBA Editor

You can view the set of Excel Events that are available for the Workbook, Worksheets or Charts in the Excel VBA Editor. Just open up the code window for the object and, in the left drop-down menu, at the top of the window, select the object type. The dropdown menu in the right window will then display the events that are defined for that object. This is shown below, for the Excel Worksheet:

Excel Events for Worksheet shown in the Excel VBA Editor

If you click on an event in the drop-down menu above the vba code window, a subroutine will automatically be inserted into the code window for that object. The arguments that Excel automatically feeds into that function (if there are any) are included into the Subroutine header - you then just need to add the vba code to define what you want to do when that event is fired.


Example

The following example displays a message box every time the cell B1 in the Worksheet "Sheet1" is selected.

For this action, we need to use the Worksheet Event "Selection_Change", which 'fires' every time a different cell or range of cells is selected. The "Selection_Change" function receives, as an argument, a Range object called "Target". This tells you the range of cells that has been selected.

As the "Selection_Change" event relates to any new selection, we need to check the supplied Range, 'Target', as soon as the 'Worksheet_Selection_Range' function is entered, so that we can perform our specific actions, only when the cell B1 has been selected. The code for this is shown below:

' Code to display a Message Box if Cell B1 of the current
' Worksheet is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' Check if the selection is cell B1
    If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then

        ' The selection IS cell B1, so carry out required actions
        MsgBox "You have selected cell B1"

    End If

End Sub
Go To Excel VBA Tutorial Part 10 - VBA Errors

Return to the Excel VBA Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net