Excel VBA – Events in Excel

Excel VBA – Events in Excel: Events are actions performed by users which trigger Excel VBA to execute code.

Workbook Open Event

Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

1. Open the Visual Basic Editor.

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

3. Choose Workbook from the left drop-down list. Choose Open from the right drop-down list.

4. Add the following code line to the Workbook Open Event:

5. Save, close and reopen the Excel file.

Result:

Worksheet Change Events

Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

1. Open the Visual Basic Editor.

2. Double click on a sheet (for example Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.

Add the following code lines to the Worksheet Change Event:

4. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to do something if something changes in cell B2. To achieve this, add the following code lines:

5. We only want Excel VBA to show a MsgBox if the user enters a value greater than 80. To achieve this, add the following code line between If and End If.

6. On Sheet1, enter a number greater than 80 into cell B2.

Result:

Benefits of Using Events Macros

There are many benefits to using event macros in Excel. Some of the benefits include:

• Save time. Event macros can automate many tasks in Excel, which can save you time. For example, you can use an event macro to open a specific worksheet when the workbook is opened, or to calculate the values in a worksheet when the worksheet is changed.
• Improve productivity. Event macros can help you to improve your productivity by automating tasks that you would otherwise have to do manually. This can free up your time so that you can focus on other tasks.
• Increase accuracy. Event macros can help you to increase the accuracy of your work by automating tasks that are prone to errors. For example, you can use an event macro to calculate the values in a worksheet, which can help to prevent you from making errors in your calculations.
• Improve consistency. Event macros can help you to improve the consistency of your work by automating tasks that are performed in a similar way. For example, you can use an event macro to format a worksheet in a consistent manner.
• Increase security. Event macros can help you to increase the security of your workbooks by preventing users from making changes to certain cells or ranges of cells. For example, you can use an event macro to protect a worksheet so that users cannot make changes to the values in the worksheet.

Conclusion

Event macros are a powerful tool that can be used to automate many tasks in Excel. By using event macros, you can save time, improve your productivity, and increase the accuracy and consistency of your work.