Free Excel Training - Excel VBA Workbook Events

Microsoft Office Training > Excel Training > Free Computer Training >Excel VBA Workbook Events

Please feel free to use this content on your web site, but please include the links at the end

Excel VBA Workbook Events

These sub procedures are executed automatically when a certain event takes place in the workbook.  Events such as opening or closing a worbook, activating a workbook, entering data into a workbook or saving a workbook can trigger sub procedures.

These sub procedures need to be stored in the code window for the workbook object.

Prevents a Save As

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

Tiles open workbooks when a workbook is selected

Private Sub Workbook_Activate()
MsgBox "Open workbooks will now be tiled"
Application.Windows.Arrange xlArrangeStyleTiled
End Sub

Moves a new worksheet so that it becomes the last worksheet in the workbook

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

Displays the sheet name and address of the selected range in the status bar.

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

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Excel Training. Microsoft Excel Training Surrey