Microsoft Excel Event-Driven Programming - How to Attach a Macro to the Event of Opening a Workbook

I don't think I have a perfect answer for this, this is what I think.

Firstly, it is seldom good idea to pick up a book on Excel programming and start reading it from cover to cover. It is better to learn when there is a specific task at hand. Excel's macro recorder is a great way to begin: start the recorder, do the steps manually, and then examine the code.

The next step is to learn to modify and optimize the code generated from the macro recorder. For example, if the step needed is to change the background colour of the cell A1 to yellow and put in the number 34 into it, the macro recorder generates the following code:

Range("A1").Select

With Selection.Interior

.ColorIndex = 6

.Pattern = xlSolid

End With

ActiveCell.FormulaR1C1 = "34"

Range("A2").Select

This code can be simplified, the underlying ideas being:

1. The above code can be written into 2 lines:

Range("A1").Interior.ColorIndex = 6

Range("A1") = 34

The code is not only smaller but also more efficient: it does only the required tasks and nothing more. Learning to code this way takes some practice. Excel's VBA help is a great resource. Sometimes it helps to just look at the example in the help first than read the details.

Once mastery of basic code is achieved, it is the right time to pick up a book on VBA coding and explore greater depths and possibilities.

Once you know VBA, it is fun too ! I have had great satisfaction playing with code and watching it do exactly what I want as I sipped coffee!

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. Event-Handling

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.

One way to do this is to press ALT + F11.

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)
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.

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.