How To Use Workbook Events in VBA

You may want to run your macro/VBA snippet when a specific workbook is selected, A sheet in the workbook is selected, cell changes its value, when a double click happens, when a sheet is added, etc. In all these cases we use Workbook Event Handler. The Event Handler helps us run VBA code whenever a certain event occurs.

In this article, we will learn briefly about each Workbook Event Handler.

What is a Workbook Event Handler?

A workbook event handler is a subroutine that is local to a workbook. These code work only on the components of a workbook. That are the workbook itself, it's sheets and ranges.

Where to write Workbook Event Handler Code?

The workbook events are written in the workbook object only. If you write a workbook event in some normal module, there will be no error but they will just not work.

To write in the workbook object. Double click on it or right-click and click on the view code. The code writing area will be shown.

How to write code for a specific event in the workbook?

Now when you are in the editing mode, in the top-left corner dropdown menu, you will see general. Click on the drop-down and select the workbook. In the top-right corner dropdown, all events will show. Choose whichever you need and a skeletal code for that event will be written for you.

Each event has a fixed procedure name. These are the reserved subroutine names that start with workbook_. You can't use them for other subroutines
(you can, but they will be normal subroutines).

Important: Each subroutine from that list will run on the specified event.

One type of workbook event procedure can be written only once on one workbook. If you write two same event handling procedures in one workbook, it will result in an error and none of them will be executed. Of course, the error will be ambiguous subroutines.

Let's learn briefly about each of the events.
1. The Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range) Event

This event triggers when we make any change to containing worksheets (formatting excluded). If you want to do something if any change made in any sheet then the code will be:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'do something 
  Msgbox "done something"
End Sub

The "Sh" is always the active sheet. The "Target" is the Active cell always.

Another example: You may want to put date and time in Cel,prl B1 if A1 changes. In that case, we use the workbook_sheetchange event. The code would look like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Target.Address = "$A$1" Then
  Range("B1").Value2 = Format(Now(), "hh:mm:ss")
 End If
End Sub

This will target only the cell A1 on every sheet since we have not specified the "sh" object.

2. The Workbook_Activate() Event

This event is triggered when the event code containing workbook activates. The skeletal code for this event is:

Private Sub Workbook_Activate()

End Sub

A simple example is showing the workbook name when it gets selected.

Private Sub Workbook_Activate()

  MsgBox "You are on workbook " & Activeworkbook.Name

End Sub

As soon as you will come on the workbook that contains this code, the event will run and will be shown a message that "You are on workbook name" (sheet2 is in my case).
3. The Workbook_Open() Event

This one of the most asked question that how to run a macro as soon as the workbook opens. This is the answer. This workbook event is runs as soon as the workbook is opened. Unlike Workbook_Activate() this code runs only once, not when every time it is activated.

Private Sub Workbook_Open()
'your code
'
End Sub

The below example Workbook_Open event will simply pop up a welcome message, when you open the code containing workbook.

Private Sub Workbook_Open()
  MsgBox "Welcome to the Master File"
End Sub

4. The Workbook_Deactivate() Event

This event triggers when leaving the code containing workbook. In other words, if you want to do something, like hiding sheets or anything when you switch workbook, use this VBA event. The syntax is:

Private Sub Workbook_Deactivate()
'your code
'
End Sub

The below example Workbook_Deativate event will simply pop up a message that you have left the master sheet, when you will leave this sheet.

Private Sub Workbook_Deactivate()
  MsgBox "You Left The Master Sheet"
End Sub

5. The Workbook_BeforeClose() Event

This event triggers when you confirm the deletion of the VBA event containing sheet. The syntax is simple:

Private Sub Workbook_BeforeClose(Cancel as Boolean)

End Sub

The Cancel can be set to true if you want to keep the workbook open.
The below code will ask you if you want to save the content of the about-to -close workbook.

Private Sub Workbook_BeforeClose(Cancel as Boolean)
    ans = MsgBox("Do you want to save the content of this workbook?", vbYesNo)
    If ans = True Then
    thisworkbook.save
 End If
End Sub

6. The Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Event

This event triggers when before the workbook is saved. The syntax is simple:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

SaveAsUI is set True if there is a change in the workbook (not in VBA).

The Cancel can be set to true if you want to keep the workbook unsaved.

The below code will ask you if you want to save the content of the about-to -save workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,Cancel as Boolean)
    ans = MsgBox("Do you really want to save the content of this workbook?", vbYesNo)
    If ans = False Then
    Cancel = True
 End If
End Sub

7. The Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Event

This event triggers when before the workbook is saved. The syntax is simple:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

SaveAsUI is set True if there is a change in the workbook (not in VBA).

The Cancel can be set to true if you want to keep the workbook unsaved.

The below code will ask you if you want to save the content of the about-to -save workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,Cancel as Boolean)
    ans = MsgBox("Do you really want to save the content of this workbook?", vbYesNo)
    If ans = False Then
    Cancel = True
 End If
End Sub

8. The Workbook_NewSheet(ByVal Sh As Object) Event

This event fires when you add a new sheet to the workbook. The syntax is simple:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub

The Sh is the sheet object. The type is actually is an core object so that if we add a chart sheet, a macro sheet or a dialog sheet, the event still works.

The below code will add and show the name of the sheet that is newly added.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 MsgBox "You added a new sheet. " & Sh.Name
End Sub

The are many more events of the workbook object. We can't discuss them all here. If you want to know about some specific event then ask in the comments section below. I hope I was able to explain the basics of the workbook events in this article. Let me know if it helped you in the comments section below.
Related Articles:

Using Worksheet Change Event To Run Macro When any Change is Made | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.

Run Macro If Any Change Made on Sheet in Specified Range | To run your macro code when the value in a specified range changes, use this VBA code. It detects any change made in the specified range and will fire the event.

Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.