myITSchools

WORKBOOK EVENTS IN EXCEL VBA

VBA workbook events are defined as an action performed by a user in Microsoft Excel that can trigger the execution of a specified macro.

For example, when a user opens a workbook in Excel, the event “Workbook_Open” event  is triggered. Similarly, when the user saves the current workbook, the event “Workbook_BeforeSave” is initiated.

These events would be triggered based on the actions at the workbook level

Examples of workbook events

  1. Adding a new worksheet
  2. Saving the workbook
  3. Opening the workbook
  4. Printing a part or the entire workbook

1) Workbook Open Event

  1. Display a welcome message as soon as user opened the workbook
  2. When we want to record the time stamp every time a user opens the workbook.
  3. When you want to display a reminder to the next user who opens the workbook
  4. When you want to display a message on a specific day of the week when the workbook is opened.
  5. When you want to always activate one specific worksheet in the workbook when it’s opened.
  6. When you want to open related files along with the workbook.

Example 1,

As Soon As user will open workbook a welcome message will show with user name

Private Sub Workbook_Open()
MsgBox “Welcome ” & Application.UserName
End Sub

Example 2,

As Soon As user will open workbook user get message to fill the timesheet according to weekday.

Private Sub Workbook_Open()
wkday = Weekday(Date)
If wkday = 6 Then MsgBox “Remember to Fill the Timesheet”
End Sub

2) Workbook NewSheet Event

NewSheet event is triggered when you insert a new sheet in the workbook.

Example 1,

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range(“A1”) = Format(Now, “dd-mmm-yyyy hh:mm:ss”)
End Sub

Example 2,

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
With Sh.Range(“A1”)
.Value = “S. No.”
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With
For i = 1 To 100
Sh.Range(“A1”).Offset(i, 0).Value = i
Next i
Sh.Range(“A1”, Range(“A1”).End(xlDown)).Borders.LineStyle = xlContinuous
End Sub

3) Workbook BeforeSave Event

The Workbook BeforeSave event is activated when an Excel user saves the current workbook, and it is triggered even before the workbook is saved.

The two possible scenarios when the Workbook BeforeSave Event can be triggered include:

  1. When saving the workbook for the first time – In this case, it will display the Save As dialog box so that the user can specify the preferred location where the file will be saved.
  2. The workbook is already saved – When such an event is triggered, it will overwrite the changes in the saved version.

Example 1,

Suppose you have a new workbook that you’re saving for the first time, and you want to remind the user to save it in the D drive, then you can use the below code:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then MsgBox “Save this File in the K Drive”
End Sub

Example 2,

  • This example could be to update the date and time when the file is saved in a specific cell.
  • The below code would insert the date & time stamp in cell A1 of Sheet1 whenever the file is saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets(“Sheet1”).Range(“A1”) = Format(Now, “dd-mmm-yyyy hh:mm:ss”)
End Sub

4) Workbook BeforeClose Event

The Workbook BeforeClose event is triggered as soon as the workbook is closed. The VBA code is executed regardless of whether the workbook is closed or not.

Example 1,

The below code protects all the worksheets before the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel as Boolean)
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Protect
Next sh
End Sub

5) Workbook BeforePrint Event

  • When you give the print command (or Print Preview command), the Before Print event is triggered.
  • The below code would recalculate all the worksheets before your workbook is printed.

Example 1,

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.PageSetup.LeftFooter = “Printed On – ” & Format(Now, “dd-mmm-yyyy hh:mm”)
Next ws
End Sub

Scroll to Top