13. VBA Tips - Enable / Disable Events
In VBA you can use events to capture certain things that happen on a workbook
- If you use Events in your spreadsheet system, it is important to understand that they are fired off by default when VBA code is run
- If you are running code in other VBA modules, it is important to TURN OFF EVENTS at the start of your procedure, and to not forget to turn them back on again when your code has finished or they will not be active again until you reopen the workbook.
Here's a screen shot of the VBA code we will use in our example:
- We have a sub routine that is fired off when a user changes the selection
- That exists in the module of sheet1
- We have some more code in Module1 that is called: RunStuff
- RunStuff selects the next cell
- If we run RunStuff without turning off events at the beginning of the code, a messagebox will appear mid run (which we don't want)
- If we forget to turn Events back on when exiting the code, they will not run again.
In summary: Use events by all means, but be very diligent when writing extra code and make sure you turn the events off at the start, and back on at all exit points of the procedure.
As a general rule, Do not write extra code that relies on events being run... it's just not good design.
Training Video on how to Enable Events in Excel: