Userform Events

What are Events?

Anything you do in to trigger an excel file is an event (an action). Example: If you want a greeting message ‘Good Day’ whenever an excel file is opened. Or if you want to capture the date and time automatically whenever a specific range of cells are altered etc.

Few examples of excel event are as follows:

• Opening a workbook
• Going to a specific worksheet
• Editing a worksheet
• Entering data
• Saving/closing file

We can use these events by adding VBA codes (event handlers) to make excel perform in a certain way.

Userform Events

Userform events trigger wherever you initialize or display the form, drag and drop the controls and perform some actions. Some of the most used workbook actions that trigger the Event-

Activate: When you activate or display the userform.

AddControl: When any userform’s control is added at the run time

BeforeDragOver: When the mouse pointer is hovering over the form and the drag and drop property is in development.

BeforeDragOrPaste: When you click the left button of the mouse, and the data is either dropped or pasted.

Click: When the left button of the mouse is clicked while the mouse pointer is over the userform.

DblClick: When the left button of the mouse is double clicked while the mouse pointer is over the userform.

Deactivate: When the userform loses focus because a subform is displayed.

Initialize: When you load the userform into memory (or you have run the userform’s code).

KeyDown: When a key is pressed while exhibiting the userform.

KeyPress: When you press an Ansi key while exhibiting the userform.

KeyUp: When you release a key while exhibiting the userform

Layout: When the size of the userform or any control is altered at run-time.

MoveDown: When the mouse button is pressed while the mouse pointer is over the userform.

MouseMove: When the mouse pointer is moved from one place to another over the userform.

QueryClose: Before you close the userform or unload it from the memory.

RemoveControl: When you remove any control from the userform at run time.

Resize: When you resize the userform.

Scroll: When you scroll the userform.

Terminate: When userform is removed from memory to clean up the memory space.

Zoom: When you zoom the userform.

Example 1

‘UserForm Deactivate event
 Sub UserForm_Deactivate()
     UserForm.Caption = "Deactivating the UserForm!"
 End Sub 

Example 2

‘UserForm click event
 Sub UserForm_Click()
     Load UserForm
     UserForm.StartUpPosition = 2
     UserForm.Show
 End Sub 

Example 3

‘UserForm click event
 Private Sub UserForm_Resize()
     UserForm1.Caption = "Click me to resize"
 End Sub 

Workbook Events

Events for workbooks need to be stored in the ‘ThisWorkbook’ module whereas events for a worksheet need to be stored in the code module for that specific sheet only.

To look at Workbook events –
• Open Visual Basic editor
• Go to ‘ThisWorkbook’ module
• From the left drop-down, select ‘Workbook’
• Now you will notice that on the right drop-down, ‘Open’ is present and in the program editor a code shell has been automatically appeared (starts with Private Sub and ends with End Sub)
• We can add anything we want excel to perform whenever the excel file is opened
• In order to look at other events available, go to the right-drop down and click on the ‘Combo Box’ to see more options

Some of the most used workbook actions that trigger the Event-

• Open - The workbook is opened.
• Activate - The workbook is activated.
• BeforeClose - The workbook is about to be closed.
• BeforeSave - The workbook is about to be saved.
• NewSheet – Whenever a new sheet is created in the workbook.
• SheetActivate – Whenever any sheet in the workbook is activated.
• SheetChange - Any worksheet in the workbook is edited/changed by the user
• WindowActivate - Any window of the workbook is activated.

Workbook Open Event Examples

This event is triggered when the workbook is opened, and this executes the Workbook_Open procedure. As stated earlier, it can be used to achieve a lot of things like –

• A Greeting Message
• Activating a specific sheet or a specific cell in a sheet
• Saving date and time or name of the user who opened the workbook

But for Workbook_Open event to fire, VBA macros should be enabled – so if the default setting is that the macros are disabled, whenever you open the file, the event will not execute on its own (till the time the macros are not enabled).

The following example gives a greeting message whenever someone opens the file with today’s date –

Private Sub UserForm_Resize()
     UserForm1.Caption = "Click me to resize"
 End Sub 

Open Event Examples

This event is triggered when the workbook is opened, and this executes the Workbook_Open procedure. As stated earlier, it can be used to achieve a lot of things like –

• A Greeting Message
• Activating a specific sheet or a specific cell in a sheet
• Saving date and time or name of the user who opened the workbook

But for Workbook_Open event to fire, VBA macros should be enabled – so if the default setting is that the macros are disabled, whenever you open the file, the event will not execute on its own (till the time the macros are not enabled).

The following example gives a greeting message whenever someone opens the file with today’s date –

Example 1

 Private Sub Workbook_Open()
 Msg = “Hello!! ” & Now()
 MsgBox Msg, vbInformation
 End Sub

Below is an example of saving a person’s name when he opens the file – as soon as the person opens an excel file which has the below macro, VBA will open another file ‘Module 10 - VBA.xlsx’ which will store the name of the person who opened the file along with the time. This type of code is used a lot to keep an audit trail of who accesses the file -

Example 2

Private Sub Workbook_Open()
 Dim xOpened As String
 Dim xtime As String
 xOpened = Environ("Username")
 xtime = Format(Now, "dd/mm/yyyy hh:mm")
 Workbooks.Open ("C:\PATH TO SECOND FILE\Module 10 - VBA.xlsx")
 Workbooks("Module 10 - VBA.xlsx").Activate
 i = Workbooks("Module 10 - VBA.xlsx").Sheets("Audit").Range("A65536").End(xlUp).Row
 Workbooks("Module 10 - VBA.xlsx").Sheets("Audit").Cells(i + 1, 1).Value = xOpened
 Workbooks("Module 10 - VBA.xlsx").Sheets("Audit").Cells(i + 1, 2).Value = xtime
 Workbooks("Module 10 - VBA.xlsx").Save
 Workbooks("Module 10 - VBA.xlsx").Close
 End Sub

You can also use conditional constraints or loops –

Example 3

 Sub Workbook_Open()
 If Weekday(Date, vbSunday) = 2 Then
 Msg = "Hello!!" & Now
 MsgBox Msg, vbInformation
 End If
 End Sub
 

We can also select a specific cell when the excel file is opened –

Example 4

Private Sub Workbook_Open()

Worksheets("Sheet1").Range("D4").Select

End Sub

Newsheet Event

The following procedure executes whenever a new sheet is added to the workbook. The sheet is passed to the procedure as an argument/parameter. Because a new sheet can be either a workshe or a chart sheet, this procedure determines the sheet type. If it’s a worksheet, it inserts a date and time stamp in cell A1.

Example 1

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 If TypeName(Sh) = “Worksheet” Then _
 Range(“A1”) = “Sheet added “ & Now()
 End Sub

BeforeSave Event

This event triggers just before the workbook is saved – so as soon as you press Cntrl+S or click on Save button, the event will trigger.
Below is an example of a file which is being saved for the first time because that will throw the prompt of ‘Save As’ (if a file is in read-only mode, then also the below event will trigger) –

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Save the file with xlsm i.e. macro-enabled format for enabling macros."
End If
End Sub

If the Save As box appears, i.e. the file is being saved for the first time (or in read-only mode).

WorkSheet Events

These events are for individual worksheets and are hence stored in the code module of the worksheet for which we plan to use them –

• Activate - The worksheet is activated.
• BeforeDoubleClick - The worksheet is double-clicked.
• BeforeRightClick - The worksheet is right-clicked.
• Calculate - The worksheet is calculated (or recalculated).
• Change - Cells on the worksheet are changed by the user.
• PivotTableUpdate A PivotTable on the worksheet has been updated.
• SelectionChange The selection on the worksheet is changed.

Worksheet_Change Event

It will trigger whenever a specified range or cell in a worksheet is changed by the user (not by a formula) – the code below will add the current date and time to column A of the row where change has taken place. The range we are interested in is from column B to column G – so if a user makes any changes to say cell B5, current date and time will be added to A5 – any changes to cell G10 and date and time will be added to A10 (and so on..)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:G")) Is Nothing Then
Cells(Target.Row, 1) = Now
End If
End Sub

Target argument/parameter has been defined as a Range object and refers to the cells which were changed – so if the user is making any changes to cell B5 – target variable will hold the value B5 – using intersect function, VBA then checks if there is an intersect i.e. common cell between target and range we have specified – and using ‘Not’ and ‘Nothing’ we are just saying that if there is an intersect, we want to add the date and current time to the row in which change took place.

Worksheet_Activate Event

This event executes whenever we go back to a specific sheet (which has the VBA code in its module) – the below example just throws a message box cautioning the user against deleting any formulas while he is on this sheet –

Private Sub Worksheet_Activate()
MsgBox "Do not delete the formula", vbOKOnly
End Sub