Microsoft Office Tutorials and References
In Depth Information
Getting Acquainted with Workbook-Level Events
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = “Worksheet” Then _
Range(“A1”).Select
End Sub
The following procedure demonstrates an alternative method that doesn’t require checking the
sheet type. In this procedure, the error is just ignored.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Range(“A1”).Select
End Sub
The NewSheet event
The following procedure is executed whenever a new sheet is added to the workbook. The sheet
is passed to the procedure as an argument. Because a new sheet can be a worksheet or a chart
sheet, this procedure determines the sheet type. If it’s a worksheet, the code adjusts the width of
all columns and inserts a date and time stamp in cell A1 on the new sheet.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If TypeName(Sh) = “Worksheet” Then
Sh.Cells.ColumnWidth = 35
Sh.Range(“A1”) = “Sheet added “ & Now()
End If
End Sub
The BeforeSave event
The BeforeSave event occurs before the workbook is actually saved. As you know, choosing the
File Save command sometimes brings up the Save As dialog box. This dialog box appears if
the workbook has never been saved or if it was opened in read-only mode.
When the Workbook_BeforeSave procedure is executed, it receives an argument
( SaveAsUI ) that indicates whether the Save As dialog box will be displayed. The following
example demonstrates this:
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox “Make sure you save this file on drive J.”
End If
End Sub
 
Search JabSto ::




Custom Search