Excel VBA Workbook Events – Macros

These sub procedures that deal with certain Excel VBA Workbook Events need to be stored in the code window for the workbook object.   These short VBA snippets are installed in the workbook itself, in the code window of the ThisWorkbook object.

  • snippetsOpen with Excel – open the target spreadsheet into which these macros are to be installed.
  • Open the Excel VBA Editor with Alt+F11
  • Right Click ThisWorkbook
  • Select View Code.  A new code window  will appear

What are Excel VBA Workbook Events?

Whether you know it or not, every workbook triggers events for even the most routine tasks such as opening, closing, saving, or exiting.   By preempting (or redefining) the default function, you can change the default behavior of Excel to do different tasks.

Prevents a Save As

There are situations that you do not want the user to create a copy of the workbook you have provided.   All changes made to  the spreadsheet must be saved back to the  original location.  This is done by redefining the Excel VBA Workbook Event for SaveAs.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then MsgBox "You cannot perform a SaveAs on this file. Save the file using the existing save settings." Cancel = True
End If
End Sub

Clean Up the Workspace

This macro arranges the open workbooks in tiled format  when a workbook is selected.  This helps the user to clean up and organize the workspace whenever they open a new workbook.  The VBA Workbook Event preempted is Workbook_Activate().

Private Sub Workbook_Activate()
MsgBox "Open workbooks will now be tiled"
End Sub

Organize the Order of Worksheets

When a user opens a new worksheet, the last worksheet opened becomes last worksheet in the workbook.  The Excel VBA Workbook event preempted is Workbook_NewSheet()

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox “The new sheet will appear as the last sheet in this workbook."
Sh.Move After:=Sheets(Sheets.Count)
End Sub

Display Sheet and Range Info in Status Bar

The Status Bar display is optional, but I find it particularly useful: it can contain lots of information, and provide some shortcuts.  I keep it turned on all the time.  This Excel VBA Workbook Event Macro displays the sheet name and address of the selected range in the status bar.  This comes in handy when working with multiple workbooks and worksheets, and when scrolling so the active cell is off the screen.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range)
Application.StatusBar = "You are in " & Sh.Name & " and have clicked in the cell range " & Target.Address
End Sub

Code By: Chester Tugwell Article Directory: http://www.articledashboard.com

Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Excel training courses as well as other Microsoft Office training options. More free computer training materials are available via the Blue Pecan website.

Narrative and explanation of the subroutines are by Rich Moyer

Rich Moyer is Principal Consultant(retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.