Workbook Events

Here are some commonly used workbook events.

Code for workbook events are saved in the ThisWorkbook module.

At the top of the code window change General to Workbook in the first drop down menu.

From the second drop down menu at the top of the code window, select which event you want to write a procedure for.

Example Workbook Event Procedures

The examples below are intended to illustrate the workings of each event rather than to provide commercially useful procedures.

Workbook Open Event

This procedure shows a welcome message in the status bar and window caption.  It also presents the user with a welcome message on opening the workbook.

Private Sub Workbook_Open()
Dim UserName As String
UserName = Application.UserName

'Display welcome message on open
MsgBox "Hi There " & UserName & ", the time and date is is " & Now

' Display message in status bar and window caption
Select Case Time
    Case Is < 0.5
        With Application
            .StatusBar = "Good Morning " & UserName _
            & ". You opened this file at " & Time
            .Caption = "Good Morning " & UserName _
            & ". You opened this file at " & Time
        End With
    Case 0.5 To 0.75
        With Application
            .StatusBar = "Good Afternoon " & UserName _
            & ". You opened this file at " & Time
            .Caption = "Good Afternoon " & UserName _
            & ". You opened this file at " & Time
        End With
    Case Is > 0.75
        With Application
            .StatusBar = "Good Evening " & UserName _
            & ". You opened this file at " & Time
            .Caption = "Good Evening " & UserName _
            & ". You opened this file at " & Time
        End With
End Select

'If weekday is Monday display XYZ report reminder
If Weekday(Date) = vbMonday Then _
MsgBox "It's Monday again, you need to file the XYZ Report"
End Sub 

New Sheet Event

The New Sheet event creates an argument called Sh. Sh represents the worksheet that has been created.  In the example code below we use Sh to name the new sheet.

Sh.Name = SheetName

This example procedure  asks the user to name the sheet when it is created.  The user input is stored in a variable called SheetName.  The date and time the sheet is created is displayed in cell A1.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim SheetName As String
Dim UserName As String
UserName = Application.UserName
SheetName = _
InputBox("You are adding a new sheet.  What would you like to call it?")
Sh.Name = SheetName
Range("A1") = "Sheet Created by " & UserName & " on " & Now
End Sub

Sheet Activate Event

The Sheet Activate event also creates an argument called Sh which represents this worksheet that has just been activated.  This procedure displays a welcome message when any sheet is activated.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "Welcome to worksheet: " & Sh.Name
End Sub

After Save Event

This procedure displays a message confirming the file name and path of the workbook after it has been saved.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim ThisWorkBookName As String
Dim ThisWorkBookLocation As String
ThisWorkBookName = ActiveWorkbook.Name
ThisWorkBookLocation = ActiveWorkbook.Path
MsgBox "You just saved " & ThisWorkBookName & " to " & ThisWorkBookLocation
End Sub

Before Close Event

The Before Close event creates a Cancel argument which if set to True will prevent the file being closed.  In this example the procedure will only let the user close the file if it is home time.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Time < "18:00" Then
MsgBox "It's not time to go home yet. Carry on working."
Cancel = True
End If
End Sub

Before Print Event

The Before Print event has an argument called Cancel which can used to cancel the print.  The procedure below requires the user to enter a password before printing.  They get three attempts: everytime they get the password wrong the Cancel argument is set to True.

The procedure also hides column B and C before printing and then calls a separate procedure to unide them (the UnhideColumnsAfterPrint procedure would be stored in a normal module).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Password As String
Dim x As Integer
'Ask for password before printing (3 attempts)
For x = 1 To 3
    Password = InputBox("Please enter password to print")
    If Password <> "1234" Then
        Cancel = True
        MsgBox "Sorry that password is not recognised. You have " _
        & 3 - x & " attempts remaining"
        Else: Exit For
    End If
Next x

'Hide columns B and C before printing
Worksheets("Printing With Hidden Columns").Range("B:C")._
EntireColumn.Hidden = True

'Run the UnhideColumnsAfterPrint procedure one second after the event
Application.OnTime EarliestTime:=Now + TimeValue("0:00:01"), Procedure:="UnhideColumnsAfterPrint"
End Sub

Window Resize Event

The Window Resize event has an argument called Wn which represents the workbook’s window.  The example procedure maximises the window whenever the user resizes it.

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Wn.Caption = "STOP TRYING TO RESIZE ME!!!!!"
Wn.WindowState = xlMaximized
End Sub

Worksheet Events

Here are some commonly used worksheet events.

Worksheet events are placed in the relevant worksheet module.

At the top of the code window change General to Worksheet in the first drop down menu.

From the second drop down menu at the top of the code window, select which event you want to write a procedure for.

Worksheet Change Event Examples

Automatically Save Workbook if Cell’s Value Change

This procedure automatically saves the workbook if any cell in the specified range changes.  The If’s condition uses the Intersect method. Intersect, as it name suggests returns the intersecting range of cells that result when two or more ranges overlap.  If no cells overlap intersect will return nothing.

Intersect(FirstRange, SecondRange etc)

The procedure is automatically run with the Worksheet Change event. Target is the range that has been changed. The Target range is automatically returned by the Worksheet Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:D10")) Is Nothing Then
    Exit Sub
Else: ThisWorkbook.Save
End If

End Sub

This version will save the workbook if any cell is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, ActiveSheet.UsedRange) Is Nothing Then
    Exit Sub
Else: ThisWorkbook.Save
End If

End Sub

Automatically Add Comments When Cell’s Value Changes

In this example the Worksheet change event triggers a procedure that automatically adds a comment to a cell.  The comment includes the date and time of the cell entry, the value entered in the cell and the username of the person who added the enty.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCell As Range
Dim Username As String
Username = Application.Username

'If cell's content is deleted, clear comments
For Each MyCell In Target
    If MyCell.Value = "" Then
        Target.ClearComments
        Exit Sub
    End If
Next MyCell

For Each MyCell In Target

'If cell doesn't have any comments add comment...
If MyCell.Comment Is Nothing Then
    '...displaying current data and time, cell's value and username
    MyCell.AddComment Now & " - " & MyCell.Value & " - " & Username

'If cell already has comment add new text to existing comment, _
do not overwrite.
Else
   MyCell.Comment.Text _
   Text:=vbNewLine & Now & " - " & MyCell.Value & " - " & Username, _
   Start:=Len(Target.Comment.Text) + 1, _
   Overwrite:=False
End If

'Autosize comments
MyCell.Comment.Shape.TextFrame.AutoSize = True

Next MyCell

End Sub

Validate Data on Entry

In this example the Worksheet Change event checks to see that user has entered a numeric value in the range SalesCells.  If the value is not numeric a warning message is displayed and the value is deleted.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Dim SalesCells As Range
Set SalesCells = Range("B4:G13")

'If the cell change is outside the SalesCells range exit procedure
If Intersect(Target, SalesCells) Is Nothing Then Exit Sub
   
For Each rg In Intersect(Target, SalesCells)
'If cell entry is not numeric delete entry and show message
    If Not IsNumeric(rg) Then
        MsgBox "You must enter a numeric value in this cell"
        Target.ClearContents
        Target.Activate
    End If
Next rg

End Sub

Selection Change Event

In this example, the active cell’s column and row are formatted with a grey background.  The formatting occurs on selection change.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells
    .Interior.Color = xlNone
    .Font.Color = vbBlack
End With
With ActiveCell
    With .EntireColumn
        .Interior.Color = rgbDarkGrey
        .Font.Color = vbWhite
    End With
    With .EntireRow
        .Interior.Color = rgbDarkGrey
        .Font.Color = vbWhite
    End With
End With
End Sub

Before Delete Event

This procedure prevents deletion of a sheet by temporarily protecting the workbook.

Private Sub Worksheet_BeforeDelete()
ThisWorkbook.Protect
Application.OnTime EarliestTime:=Now, Procedure:="UnProtectBook"
End Sub

OnKey Events

Use the OnKey method to assign shortcut keys to your macros.

Application.OnKey(Key, Procedure)

OnKey Parameters

Refer to specific keys using the codes shown below.

Assign a Custom Keystroke

To assign the shortcut keys automatically when the workbook opens, place the OnKey statements in the ThisWorkbook module in the Workbook Open event procedure.

Private Sub Workbook_Open()
'Setup OnKeys
Application.OnKey "+{F1}", "SHIFTF1"
End Sub

The procedure the shortcut key calls can be placed in a normal module.

Sub SHIFTF1()
Dim User As String
User = Application.Username
ActiveCell = "Created by " & User & " on " & Now
End Sub

Please note that the OnKey assignment applies to all open workbooks.

Unassign a Custom Keystroke

To unassign a custom keystoke, omit the Procedure parameter in the OnKey statement.  Place this code in the ThisWorkbook module using the Before Close event procedure.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'UnAssign OnKeys
Application.OnKey "+{F1}"
End Sub

Disable Built In Excel Keystokes

If you want to disable built in keystokes return an empty text string in the Procedure argument. The following OnKey statement disables ALT F4 which would normally close Excel.

Application.OnKey "%{F4}", ""