Excel VBA Events

In programming, Events are the actions or occurrences performed by the users. These events are triggered against some object which then executes a set of codes.

Similar to other programming languages, MS Excel VBA also supports events on its objects. Although, every control used in MS Excel VBA, has multiple events associated with them.

In this tutorial, you will learn to manage two default events associated with every VBA application – Workbook Open Event and Worksheet Change Event.

Workbook Open Event

In Workbook Open event, whenever a MS Excel file will be open, it will execute the code written under the Workbook open event. To experience the Workbook open event, follow the steps given below:

1. Open the Visual Basic Editor through Developer tab or by pressing Alt + F11 keys.

2. Double click on This Workbook in the Project Explorer.

3. Choose Workbook from the left drop-down list showing (General).

4. Choose Open from the right drop-down list (although, it will automatically be selected)

5. Add the following line of code under the Workbook Open Event:

MsgBox “Welcome to MS Excel Tutorial”

5. Save, close and reopen your MS Excel file.

Result:

Worksheet Change Event

In Worksheet Change Event, whenever a change in a cell on a worksheet occurs, the code written under the Worksheet Change event will be triggered. To experience this event, follow the steps given below.

1. Open the Visual Basic Editor through Developer tab or by pressing Alt + F11 keys.

2. Double click on Sheet1 in the Project Explorer.

3. Choose Worksheet from the left drop-down list showing (General).

4. Choose Change from the right drop-down list. (by default, it will select “SelectionChange”)

5. Add the following lines of code to the Worksheet Change Event:

Explanation: In the above code, you will observe the use of two if statements. The first If statement that uses Target.Address is basically validating the Cell. The second nested If statement that uses Target.Value is actually validating the values. If the value in Cell A1 is 75 then a Msgbox will appear stating “Goal Met” otherwise, it will say “Goal not Met”

6. On Sheet1, enter any number other than 75 in Cell A1. You will observe that a msgbox will pop stating that Goal not Met.

7. Now, on Sheet1, enter number 75 in Cell A1 and this time msgbox will pop stating Goal Met.