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

  1. Private Sub Workbook_Open()
  2. MsgBox "Welcome to VBA tutorial"
  3. 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

  1. Private Sub Workbook_Activate()
  2. MsgBox "Welcome to VBA tutorial"
  3. 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

  1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  2. 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

  1. Private Sub Workbook_BeforePrint(Cancel As Boolean)
  2. 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.

  1. Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)
  2. End Sub

Like wise , the Workbook object has many other useful events which are listed as below

  1. Aftersave Event
  2. AfterPrint Event
  3. SheetActivate
  4. SheetCalculate
  5. SheetChange
  6. SheetDeActivate
  7. AfterXMlExport
  8. AfterXMlImport
  9. BeforeXMlImport
  10. BeforeXMlExport

 

You can find similar Excel Questions and Answer hereunder

1) How can I find the number of working days between 2 dates given a holidays list?

2) how do you enter multiple line in a cell in Excel

3) How can I add a Timestamp after macro execution?

4) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

5) I want a formula to concatenate multiple cell values separated by a line break.

6) What are pivot tables. Here an introduction to understand them in Excel

7) How can I filter a table to get all records that have less than a particular value in a specific column?

8) Here some explanations about the events related to the forms and how to control them

9) How can I identify all cells with Data Validation in my WorkSheet?

10) How to you use the events when the user clicks on message boxes on the screen.

 

Here the previous and next chapter