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.
- Open 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
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"
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."
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
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