Excel 2010 Bible

Part VI: Programming Excel with VBA

878

Using the SheetActivate event

The following procedure executes whenever the user activates any sheet in the workbook. The

code simply selects cell A1. Including the On Error Resume Next statement causes the proce-

dure to ignore the error that occurs if the activated sheet is a chart sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

On Error Resume Next

Range(“A1”).Select

End Sub

An alternative method to handle the case of a chart sheet is to check the sheet type. Use the Sh

argument, which is passed to the procedure.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If TypeName(Sh) = “Worksheet” Then Range(“A1”).Select

End Sub

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 _

Range(“A1”) = “Sheet added “ & Now()

End Sub

Using the BeforeSave event

The BeforeSave event occurs before the workbook is actually saved. As you know, choosing

Office ➪ 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