Menu

Event handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

Other links

Example code snippets

Userform input example

Event Handling

An Event is something that happens in a program such as:

Opening or closing a workbook
Saving a workbook
Activating or deactivating a worksheet or window
Pressing a key or key combinations
Entering/Editing data in the worksheet
Clicking the mouse on a control/object
Double clicking on a cell
Data in a chart is updated
Recalculating the worksheet
A particular time of day occurs

You can therefore run a procedure automatically when a certain Event in Excel occurs.

There are different objects (and therefore different levels) when Excel automatically triggers a procedure as the system is constantly listening for the event to occur.

Workbook Events

Open Event

The most common type of Open Event is Workbook_Open. This procedure is executed when the workbook is opened and is often used for the tasks such as:

Displaying a welcome message
Opening other workbooks
Setting up custom menus and toolbars
Activating a particular sheet or cell

Example:

Every time the user opens the workbook, they are greeted with a message box displaying the day of the week. If it is a Friday, a message box will remind the user to submit their timesheet.

1. Open the required workbook.

2. Switch to the Visual Basic Editor.

3. Double click on ThisWorkbook from within the Project Explorer.

4. Click on the Object drop down list and select Workbook

5. Enter the following between the signature Private Sub Workbook_Open()
and End Sub keywords:

Private Sub Workbook_Open()
    MsgBox "Today is " & WeekdayName(Weekday(Now), False, vbSunday)
    If Weekday(Now) = vbFriday Then
        MsgBox "Don't forget to submit your timesheet"
    End If
End Sub

Note: Private means that the procedure won’t appear in the Run Procedure dialog box (i.e. Macros dialog). See Scope & Visibility in Variables & Constants for information.

Workbook Activate Event

The procedure is executed whenever the workbook is activated (gets the focus).

Example:

Call the signature Private Sub Workbook_Activate() using the same methods as previously explained above.

Enter the following code:

Private Sub Workbook_Activate()
ActiveWindow.WindowState = xlMaximized
End Sub

Now the window will always maximise when the workbook gets the focus.

Note: Deleting an event (the signature) will not harm the system as it is re-generated each time you call one of the pre-defined signatures.

Example:

Using the Private Sub Workbook_SheetActivate(ByVal Sh As Object)signature is triggered across any worksheet in the active workbook.

Enter the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = Date 'Enters the current date in A1
Range("A2").Select 'Position the cursor in A2
End Sub

The 'Sh' argument can also be used to refer to which worksheet is being called should you wish to control the index or name of a particular worksheet or group of worksheets.

By including a code line: If Sh.Name = “Sheet3” Then... it will handle the logic and control flow for 'Sheet3'.

Worksheet Events

Worksheet Activate Event

Within a workbook you also have separate nodes for each added worksheet chart sheet which contain a private (local) module over an above standard modules in a VBA project.

Example:

Every time the user clicks on 'Sheet1' if the first cell (A1) is empty then prompt the user with an InputBox function to enter a title.

Private Sub Worksheet_Activate()
    If Trim(Range("A1").value) = Empty Then
        Range("A1").Value = Trim(InputBox("Enter title:"))
        Range("A1").EntireColumn.AutoFit
    End If
End Sub

Note: If there are events at both the worksheet and workbook level which point to the same object (worksheet), then it's the worksheet level will run first followed by the workbook event.

Other Events

There are other ways to get Excel to trigger a macro using other events from other objects or controls.

It is possible to attach procedures to the ActiveX Controls so that whenever the user clicks onto a control, the procedure will run.

Example:

When the user clicks on the Command Button, a message box will appear.

1. From Excel, click on the Developer tab (Ribbon Bar), select Insert icon
and choose Button icon from the Form Control section.

2. Draw the Command Button onto the spreadsheet.

3. The Assign Macro dialog box appears, Click the New... button.

4. Enter the following code:

Sub Button37_Click()
MsgBox "Button click event!"
End Sub

Any control drawn on a worksheet or user form will have pre-defined events that can be coded to respond by the system.

How do you think features like conditional formatting and data validation work in a worksheet when set in Excel? When the user enters a value in a cell, the Change event is triggered:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then Range("A2").Font.Bold = True
End Sub

Target is the argument to test which cell address is being changed.

Next Topic: Error Handling

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keyword: InuptBox, MsgBox, Range, WeekdayName, Weekday, Now, Date, ActiveWindow, WindowState, EntireColumn, AutoFit, Font & Bold.