Suppose that you’ve written a workbook for a client, friend or relation of yours, and you want to ensure that when said client, relation or friend opens the workbook, Excel selects the correct cell and displays a nice cuddly welcoming message. How to do this?
Event-Handling
All the time that you use Excel, hidden events are firing (that’s the technical term for happening, by the way). Events can happen at two levels – either at the workbook level, or at the worksheet level.
Some examples of workbook-level events are: opening a workbook, closing it, printing it and activating a workbook.
Some examples of worksheet-level events are: selecting a cell and activating the worksheet.
Viewing the Code Editor
To attach code to a workbook, the first thing that you need to do is to view the VBA code editor. One way to do this is to press ALT + F11.
You will then see all of the files that you have open in Project Explorer on the left-hand side of the VBA code window (if you can’t see this, press CTRL + R). Each file open is shown in bold type. Find the file that you’re working with (if you only have one file open, this will be easy!).
Beneath the file will be listed the worksheets within it, followed by the workbook itself. These will typically have the following names:
– Sheet1 (Sheet1)
– Sheet2 (Sheet2)
– Sheet3 (Sheet3)
– ThisWorkbook
Attaching Code to the Open Event
To attach code to the worksheet-level event, double-click on one of the worksheets. However, for our case we want to attach code to run when someone opens the workbook, so just double-click on the ThisWorkbook object.
At the top left of the code window which appears is a drop down arrow, displaying the word General. Click on this arrow, and choose Workbook (it’s the only other option shown). You will now see:
Private Sub Workbook_Open()
End Sub
This is the event-handling stub for the open event for the workbook. All that you need to do is type in here valid VBA commands – for example:
MsgBox “Welcome to this workbook!”
When you save, close and reopen the file, you will see the message.
Other Things to Try
When you chose to attach code to the workbook, another drop arrow at the top right of the screen displayed the word Open. Try clicking on the arrow, and you’ll see all the other events in a workbook’s life to which you can attach macros.