Chapter 5

Managing Documents with Events

Chapter Objectives
Estimated time: 60 minutes

Use an event procedure to trap when the following File menu commands are clicked: New, Open, Close, Save, Save As, Save as Web Page, and Print—or when the same actions occur programmatically.

Understand when the new, open, save, close, and print events are triggered in Microsoft Word, Microsoft Excel, and Microsoft PowerPoint and know the differences among these events in each application.

Add or remove content such as header or footer information to or from documents when they are opened or before they are saved, printed, or closed.

Set and retrieve document properties such as Author and Title, as well as determine if a specific document property is set before you save, print, or close a document.

The main functionality for creating and managing Microsoft Office documents is reflected in the commands on the File menu in Word, Excel, PowerPoint, Microsoft Access, and Microsoft Outlook. You use the File menu, among other things, to create new documents and to open, save, close, and print existing ones. When you use the mouse or a keyboard shortcut to invoke one of these File menu commands, Office determines what command has been invoked and what action should be carried out.

When one of these actions, or events, occurs, your Microsoft Visual Basic for Applications program can respond, allowing you to determine when a document is created, opened, saved, closed, or printed. This chapter describes the event procedures that are called in your programs when any one of the following commands or their programmatic equivalents (as described in Chapter 4) are invoked through the File menu or through Visual Basic code: New, Open, Close, Save, Save As, Save as Web Page, and Print. After you set up event procedures to handle the occurrence of the actions New, Open, Save, Close, and Print, you can develop solutions like the following:

When you create or open a document in Word, Excel, or PowerPoint, you can insert or manipulate specific content.

Before you save a document, you're required to set document properties such as the Author and Title. If they aren't there, the save operation will be cancelled.

Quick Guide

This quick guide lists and describes the events you can respond to when you create, open, save, print, or close any document in Word, Excel, or PowerPoint. The steps following this quick guide also show how to set up the event procedures in your Visual Basic code. The event procedures are called whenever the user generates the action of creating, opening, saving, printing, or closing any Word, Excel, or PowerPoint document through the File menu or programmatically through code, using any of the methods described in the previous chapter.

The following table lists the events you'll call. Their names, such as NewDocument, WorkbookOpen, and PresentationSave, appear in the name of the event procedure you set up in your program.

Event Word Excel PowerPoint

New NewDocument NewWorkbook NewPresentation

Open DocumentOpen WorkbookOpen PresentationOpen

Save DocumentBeforeSave WorkbookBeforeSave PresentationSave

Close DocumentBeforeClose WorkbookBeforeClose PresentationClose

Print DocumentBeforePrint WorkbookBeforePrint PresentationPrint

Excel and PowerPoint also provide the WorkbookNewSheet and PresentationNewSlide events, which are called when a worksheet or slide is added to a workbook or presentation, respectively.

Outlook doesn't have direct equivalents to these events except for Open, Write, and Close. It has a NewInspector event that can be related to the NewDocument event, but NewInspector is triggered not just for new items, but any time an item in a folder is displayed. You can determine if the message is new by using the CurrentItem and the Sent or Saved property. Access doesn't have direct equivalents to these events.

Event	Word	Excel	PowerPoint
New	NewDocument	NewWorkbook	NewPresentation
Open	DocumentOpen	WorkbookOpen	PresentationOpen
Save	DocumentBeforeSave	WorkbookBeforeSave	PresentationSave
Close	DocumentBeforeClose	WorkbookBeforeClose	PresentationClose
Print	DocumentBeforePrint	WorkbookBeforePrint	PresentationPrint

IMPORTANT
In some cases you'll find that you can't perform certain operations when the code is executed within an event procedure. For example, if you add the line Pres.Close in the NewPresentation event procedure in PowerPoint, an error will occur indicating that this operation can't be performed in the event procedure. Also, an application may not allow your program to quit the application (that is, execute Application.Quit) in an event procedure.