VBA Events introduction in Excel
VBA comes under the category of "Event driven Programming", since it supports events.
In Programming, an "Event" is an action, which is either User action,mouse clicks, button clicks or messages from other programs and so on.
The term "Event driven" signifies the execution of code based on one or more of the above said events.
The event driven capability of programming languages have facilitated programmers and users with an enormous number of applications and Excel is no exception.
In Excel, Events are attached with almost every object in the application.
For example, a VBA button has a click event, a VBA dropdown box has select changed event and even workbooks and worksheets too have events associated with them.
Some of the events are as discussed below:
1.WorkBook open event
This event occurs whenever the workbook is opened.There are some occassions, when a code has to execute whenever the workbook opens.in such case,
this event is helpful.
The following example illustrates this
- Private Sub Workbook_Open()
- MsgBox "Welcome to VBA tutorial"
- End Sub
with the above code, a msgbox is diplayed, whenever this workbook is opened.
2.WorkBook Activate event
This event occurs whenever a workbook is activated.This differs from the Open event in the sense that, when more than one workbook is open at a time,
This event will trigger whenever a specific workbook is activated.
The following example shows this
- Private Sub Workbook_Activate()
- MsgBox "Welcome to VBA tutorial"
- End Sub
3.Workbook BeforeSave event
This event occurs whenever the workbook is saved.This is triggered exactly before the workbook is saved.
This event is useful whenever something has to be automated while saving a workbook.
The followig snippet illustrates this event
- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- End Sub
4.Workbook BeforePrint event
This event occurs whenever the workbook is about to be printed.This is triggered exactly before the workbook is printed.
This event is useful when an action has to be performed at the instant of printing.
The followig snippet illustrates this event
- Private Sub Workbook_BeforePrint(Cancel As Boolean)
- End Sub
5.Workbook SheetTableUpdate event
This event occurs whenever a table is updated in a sheet.
A table is an object and is embedded as a separate entity in a sheet and is different from ordinary excel data in tabular format.
The following code snippet demonstrates this event.
- Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)
- End Sub
Like wise , the Workbook object has many other useful events which are listed as below
- Aftersave Event
- AfterPrint Event
- SheetActivate
- SheetCalculate
- SheetChange
- SheetDeActivate
- AfterXMlExport
- AfterXMlImport
- BeforeXMlImport
- BeforeXMlExport