Excel Vba Workbook Event Macros

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

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.