Visual Basic for Applications is very much an event driven programming language and it is possible to write events for a large number of operations, including when the user changes worksheet or when they select a particular cell range.
There are a huge number of event procedures available and they can be accessed from different places.
The most popular events are split into three levels and some events can occur at more than one level
1) Application - These occur at the Excel instance level.
2) Workbook - These occur at the individual workbook level.
3) Worksheet - These occur at the specific worksheet level.
Worksheet and Workbook level events are the most popular and are available by default.
Chart - These occur for all the charts.
Class modules can be used to create event procedures for application and chart events.
You can use the EnableEvents property to enable or disable the events.
You may need to disable events in order to prevent an infinite loop occurring.
You can disable the events from being activated by using the "Application.EnableEvents = False"
When you disable events this applies to all open workbooks ??
Application.EnableEvents = False
'action that normally causes an event to be fired
Application.EnableEvents = True
You can often check the value of the cancel parameter to see what the last event event handler set it to.
This means that as an event bubbles through multiple handlers each subsequent handler can override what the previous handlers set it to.
Application level events get the final say
Application Level Events
To write event procedures for the application, you must create a new object using the WithEvents keyword in a class module.
Workbook Level Events
These can be accessed from the "ThisWorkbook" object. Select Workbook in the top left drop-down box and all the workbook level events will appear in the right drop-down box.
Worksheet Level Events
Worksheet level events procedures must be located in the code module for that particular worksheet.
Worksheet events are enabled by default and can be accessed by either right mouse clicking on a worksheet tab and selecting View Code.
An alternative to disabling the events in a Worksheet_Change event could be to declare a static boolean variable.
To write event procedures for an embedded chart you must create a new object using the WithEvents keyword in a class module.
When multiple add-ins handle the same event on the same object, you cannot rely on the order in which these events will be fired
Events are raised at Worksheet, then Workbook, then Application
Workbook, worksheets and charts are all examples of special types of class modules ??
Some actions will trigger more than one event.
Events always occur in a particular sequence and knowing the sequence is essential when writing event handlers.
Events Don't Fire ??
If you have the Analysis ToolPak VBA add-in installed then the following events will not be fired when you open Excel.
Application - WorkbookActivate
Workbook - WindowActivate
Application - WindowActivate
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited