The Document New Event

The procedure for the document New event is called after you create a new document. Word, Excel, or PowerPoint create a new document, display it, and then, if it's set up in your program, call the document New event. This event can't be cancelled. You can create new documents with the New command on the File menu or on the Standard toolbar. The Add method on the Documents, Workbooks, and Presentations collection objects, described in the previous chapter, also triggers the New event procedure.

Sub App_NewDocument(ByVal Doc As Document)
Sub App_NewWorkbook(ByVal Wb As Workbook)
Sub App_NewPresentation(ByVal Pres As Presentation)

There are no differences in how the New event is defined in each application or when it's called. In all three applications, the application creates the document first and then calls the event procedure. PowerPoint calls the NewPresentation event procedure after it creates the presentation but before it displays the New Slide. As previously noted, however, PowerPoint doesn't allow your code to perform operations like closing the newly created presentation in the NewPresentation event procedure. If the NewPresentation event procedure executes the code Pres.Close, an error will occur.

Example Scenario

The following examples for Word, Excel, and PowerPoint each perform the same operation: when you create a new document, the application adds the text Created on, followed by the current date and time, to the footer at the bottom left of each page. The text format for the footer is italicized. The date and time values are static once they're inserted so their values do not change as the date and time changes. Although the code to indicate the date format m/d/yy and the hours:minutes and am/pm formats is different in each application, the same date and time format is inserted.

Set Up the NewDocument Event in Word

Before following the steps below, make sure you complete steps 1, 2, and 4 in the section "Set Up an Event Procedure" after the Quick Guide.

  1. In Word, start the Visual Basic Editor and then double-click the Class1 project item in the Project Explorer to make it the active window.

  2. Click App from the Object drop-down list and then select NewDocument from the Procedures drop-down list in the class module. In the NewDocument event procedure, add the following code:

    3. Private Sub App_NewDocument(ByVal Doc As Document)
    With Doc.Sections(1).Footers(wdHeaderFooterPrimary).Range
        .InsertDateTime DateTimeFormat:="M/d/yy h:mm am/pm", _
            InsertAsField:=False, DateLanguage:=wdEnglishUS
        .InsertBefore Text:="Created on "
        .Font.Italic = True
    End With
End Sub

    To insert a footer in Word, use the Footers property to access the footer text range of the document. You insert the date and time with the InsertDateTime method on the Range object and you insert the text 'Created on' before the date and time with the InsertBefore method.

  3. In the Project Explorer, double-click the Module1 project item to make it the active window, place the cursor in the InitEvents procedure, and press F5.

  4. Switch to Word and click the New button on the Standard toolbar. The text, date, and time are inserted at the bottom left of each page. To view the footer, click Print Layout on the View menu and scroll down to the bottom of the page.

Set Up the NewWorkbook Event in Excel

To insert a header or footer in Excel, you use the PageSetup property on the Worksheet object to access the PageSetup object. On the PageSetup object, you use the LeftFooter property to set the footer. Other similar properties are CenterFooter, RightFooter, LeftHeader, CenterHeader, and RightHeader. To insert text or the date and time in any header or footer position in Excel, you need to apply header and footer information to all the worksheets in a workbook.

Before following the steps below, make sure you complete steps 1, 2, and 4 in the section "Set Up an Event Procedure" after the Quick Guide.

  1. In Excel, start the Visual Basic Editor and then double-click the Class1 project item in the Project Explorer to make it the active window.

  2. Click App from the Object drop-down list and then select NewWorkbook from the Procedures drop-down list in the class module. In the NewWorkbook event procedure, add the following code:

    3. Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    Dim sh As Worksheet
    For Each sh In Wb.Worksheets
        sh.PageSetup _
            .LeftFooter = "&""Italic,Italic""Created on " _
            & FormatDateTime(Date$) & _
            " " & FormatDateTime(Time$) 
    Next sh
End Sub

    Excel doesn't provide the ability to insert the current date and/or time that are static and don't change. However, you can use the Date$ and Time$ functions built into the Visual Basic for Applications language. If you use &D and &T in the string assigned to the LeftFooter property, the current date and time will be displayed in the footer whenever you view the footer or print the worksheet. The string assigned to the LeftFooter property would then appear as follows: "&""Italic""Created on &D &T".

  3. In the Project Explorer, double-click the Module1 project item to make it the active window, place the cursor in the InitEvents procedure, and press F5.

  4. Switch to Excel and click the New button on the Standard toolbar.

    5. When you view a worksheet normally, you won't see the header and footer. Header and footer information is only displayed in a worksheet's Print Preview mode and on printed pages. To see or set headers and footers in Excel, click Page Setup on the File menu and click the Header/Footer tab. In this example, a custom footer is set. Therefore, to see the custom footer, click the Custom Footer button between the Header and Footer previews.

NOTE
Excel also provides an event that is called when you add a new worksheet to a workbook. The WorkbookNewSheet event procedure is called when you add a worksheet by clicking Worksheet on the Insert menu. The event procedure is defined as: 

Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _
    ByVal Sh As Object)

Set Up the NewPresentation Event in PowerPoint

In PowerPoint, you add text to the DateTime placeholder, which is located at the bottom left of the presentation's slide master by default. The index position of the DateTime placeholder in the Placeholders collection of the presentation's slide master is 3 by default. If a different default presentation template is used when you create new presentations, the DateTime placeholder may not exist. The PowerPoint example in the Print section near the end of this chapter describes how to write a function to determine if a specific placeholder type exists in a presentation.

Before following the steps below, make sure you complete steps 1, 2, and 4 in the section "Set Up an Event Procedure" after the Quick Guide.

  1. In PowerPoint, start the Visual Basic Editor and then double-click the Class1 project item in the Project Explorer to make it the active window.

  2. Click App from the Object drop-down list and then select NewPresentation from the Procedures drop-down list in the class module. In the NewPresentation event procedure, add the following code:

    3. Private Sub App_NewPresentation(ByVal Pres As Presentation)
    With Pres
        With .SlideMaster.Shapes.Placeholders(3).TextFrame
            .WordWrap = msoFalse
            .AutoSize = ppAutoSizeShapeToFitText
            With .TextRange
                .Text = ""
                .InsertDateTime _
                    DateTimeFormat:=ppDateTimeMMddyyHmm, _
                    InsertAsField:=msoFalse
                .InsertBefore "Created on "
                .Font.Italic = msoTrue
            End With
        End With
    End With
End Sub

    You use the SlideMaster object, which you access using the SlideMaster property on the Presentation object, to set footer information on all the slides in a presentation. All slides, with the exception of title slides, share the same slide master. If you set the text in a placeholder on the slide master, it applies to all slides. In the NewPresentation event procedure, you access the DateTime placeholder on the Shapes collection of the SlideMaster for the new presentation.

  3. In the Project Explorer, double-click the Module1 project item to make it the active window, place the cursor in the InitEvents procedure, and press F5.

  4. Switch to PowerPoint and click the New button on the Standard toolbar.

    5. To change the text in the Footer or Slide Number placeholder, select Header and Footer on the View menu and display the Slide tab.

NOTE
PowerPoint also provides an event that's called when you add a new slide to a presentation. The PresentationNewSlide event procedure is called when you add a slide by clicking New Slide on the Insert menu or by pasting it from another presentation. The event procedure is defined as: 

Sub App_PresentationNewSlide(ByVal sld As Slide)