Page 296 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

The following event procedure will ensure that the Excel application window is maximized
and displays a message box reminding the user of the current date:

Private Sub Workbook_Open()
Application.WindowState = xlMaximized
Msgbox "The date is:" & Date
End Sub

Note If you hold down the Shift key when you start Excel or when you open a workbook,
you will prevent the Workbook_Open procedure from executing.

Activate Event

The Activate event is triggered when the workbook is activated, such as when it is initially
opened, when switching to the Excel window after viewing another program, or when
switching between open workbooks. For example, if you want to ensure that the workbook is
maximized when working in the file, you can add the following event procedure to the
ThisWorkbook object:

Private Sub Workbook_Activate()
ActiveWindow.WindowState = xlMaximized
End Sub

SheetActivate Event

The SheetActivate event is executed when any sheet is activated within the workbook. The
event will occur regardless of the type of sheet activated, Worksheet or Chart. It’s important to
verify which type of sheet is activated. An If…Then…Else statement can be used to determine
which code will execute for each type of sheet. For example, you could select cell A1 on the
worksheet to assist the users who work with the file. The following event procedure will verify
the type of sheet and then activate cell A1:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then Range("A1").Select
End Sub

NewSheet Event
The NewSheet event is executed when a new sheet is added to the workbook. The sheet is
passed to the event as an argument so you can manipulate the created sheet. The following
procedure moves the new sheet to the end of the workbook:
Private Sub Workbook_NewSheet(ByVal Sh as Object)
Sh.Move After:=Sheets(Sheets.Count)
End Sub
Chapter 12

270
Part 4: Advanced VBA