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.
Some events can occur at more than one level:
Application - These occur at the application/instance level (requires a class module).
Workbook - These occur at the individual workbook level (available by default).
Worksheet - These occur at the specific worksheet level (available by default).
Chart - These occur for all the charts (requires a class module).
Application Level Events
You must insert a new class module into your project and create a new object using the WithEvents keyword.
More details can be found here: VBA > Events > Application Level
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.
More details can be found here: VBA > Events > Workbook Level
Worksheet Level Events
These can be accessed from the "SheetX" object.
Select "Worksheet" in the top left drop-down box and all the worksheet level events will appear in the right drop-down box.
More details can be found here: VBA > Events > Worksheet Level
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
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
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
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext