Part VI: Automating Excel
Using the NewSheet event
The following procedure executes 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 either a
worksheet or a chart sheet, this procedure determines the sheet type. If it’s a worksheet, it
inserts a date and time stamp in cell A1:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then _
Sh.Range("A1").Value = "Sheet added " & Now()
End Sub
Using the BeforeSave event
The BeforeSave event occurs before the workbook is actually saved. As you know, choos-
ing File ➪ Save sometimes brings up the Save As dialog box—for example, when the file has
never been saved or was opened in read-only mode.
When the Workbook_BeforeSave procedure executes, it receives an argument that
enables you to identify whether the Save As dialog box will appear. The following example
demonstrates this:
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Use the new file-naming convention."
End If
End Sub
When the user attempts to save the workbook, the Workbook_BeforeSave procedure
executes. If the save operation brings up the Save As dialog box, the SaveAsUI variable is
True. The preceding procedure checks this variable and displays a message only if the Save
As dialog box is displayed. In this case, the message is a reminder about how to name the
file.
The BeforeSave event procedure also has a Cancel variable in its argument list. If the
procedure sets the Cancel argument to True, the file is not saved.
Using the BeforeClose event
The BeforeClose event occurs before a workbook is closed. This event is often used in
conjunction with a Workbook_Open event handler. For example, use the Workbook_Open
procedure to initialize items in your workbook, and use the Workbook_BeforeClose pro-
cedure to clean up or restore settings to normal before the workbook closes.
If you attempt to close a workbook that hasn’t been saved, Excel displays a prompt that
asks whether you want to save the workbook before it closes.
