The Document Save Event

The procedure for the document Save event is called before you save a document. Word, Excel, or PowerPoint will call the document Save event procedure, if it's set up in your program, before saving the document and before displaying the Save As dialog box (if you haven't previously saved the document on disk). In Word and Excel, you can cancel this event. Therefore, the name of the event is prefixed with the word Before. In PowerPoint, you can't keep this event from occurring and, thus, its name isn't prefixed with Before.

NOTE
If either Word, Excel, or PowerPoint is in its sequence of closing a document and the changes to it will be saved, the application calls the document Save event procedure after the document Close event procedure.

You can save documents with the Save, Save As, or Save as Web Page commands on the File menu or with the Save command on the Standard toolbar. The Save or SaveAs method on the Document, Workbook, and Presentation objects, described in the previous chapter, also triggers the document Save event procedure.

Sub App_DocumentBeforeSave(ByVal Doc As Document, _
    SaveAsUI As Boolean, Cancel As Boolean)

Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
    ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sub App_PresentationSave(ByVal Pres As Presentation)

The main difference between the document Save event in Word, Excel, and PowerPoint is that both Word and Excel allow the developer to cancel the save action but PowerPoint does not. When the user clicks on the Save command or on a related save command, or when the Save or SaveAs method on the document object is executed, the event procedure listed above is called in the appropriate application. In Word and Excel, your code can prevent Word and Excel from actually saving the changes in the document if you set to True the Cancel Boolean argument passed into the event procedure. In Word and Excel, the second argument in the event procedure definition is the SaveAsUI Boolean argument and it allows you to set whether the Save As dialog box will be displayed.

Evaluating the Filename Entered in the Save As Dialog Box

Many common scenarios exist where you need to retrieve the filename and path that the user enters in the Save As dialog box, parse the filename, and, based on the value, perform a specific operation. For example, if the user wants to save a document to a specific folder location on the network or an HTTP server, you can make sure the user enters certain document properties before the document is saved. If the user doesn't, your code can cancel the Save event. Word and Excel provide a way to display the Office Save As dialog box and parse the entered filename to handle this scenario.

In Word, start the Visual Basic Editor and in a standard code module, insert the following procedure. Press F5 to run the procedure. The Save As dialog box is displayed when the Display method on the Dialog object is executed. After you select a file and click Save, the string value of the filename returned by the dialog box is evaluated and printed to the Immediate window. If the filename doesn't consist of a path to a Web Folder, the Save function of the Save As dialog box is executed using the Execute method of the Dialog object.

Sub UsingSaveAsDialogInWord()
    Dim dlgSaveAs As Dialog, sFileName As String
    Set dlgSaveAs = Dialogs(wdDialogFileSaveAs)
    dlgSaveAs.Display
    sFileName = dlgSaveAs.Name
    If Left$(sFileName, 4) = "http" Then
        Debug.Print "Web Folder: " & sFileName
    Else
        Debug.Print CurDir & "\" & dlg.SaveAs.Name
        dlgSaveAs.Execute
    End If
End Sub

You can use similar code in the DocumentBeforeSave event in Word to cancel the Save event if the user wants to save to a specific folder but certain conditions are not met. See the steps "Evaluate and Cancel the Document Save Event Based on the Filename " in this section for an example.

In Excel, start the Visual Basic Editor and in a standard code module, insert the following procedure. Press F5 to run the procedure. The Save As dialog box is displayed when the GetSaveAsFilename method on the Application object is executed. After you select a file and click Save, the string value of the filename returned by the dialog box is evaluated and printed to the Immediate window. If the filename doesn't consist of a path to a Web Folder, the SaveAs method of the Workbook object is used to save the active workbook with the filename specified in the Save As dialog box.

Sub UsingSaveAsDialogInExcel()
    Dim sFileName As String
    sFileName = Application.GetSaveAsFilename
    If Left$(sFileName, 4) = "http" Then
        Debug.Print "Web Folder: " & sFileName
    Else
        Debug.Print sFileName
        ActiveWorkbook.SaveAs sFileName
    End If
End Sub

You can use similar code in the WorkbookBeforeSave event in Excel to cancel the Save event if the user wants to save to a specific folder but certain conditions are not met. See the WorkbookBeforeSave event procedure in the sample file XlEvents.bas in the Chapter 5 practice folder for an example of evaluating and cancelling the workbook Save event based on the filename.

You can also display the Microsoft Windows system File dialog box, which enables your code to determine the filename, path, and file type specified by the user. For a description of how to use and display the Windows system File dialog box in this and similar scenarios, see the "File Management" section of Chapter 4.

Example Scenario

In the following examples for Word, Excel, and PowerPoint, the code in the document Save event procedure evaluates whether the user who is saving the document is logged onto the network. If the user is logged on, the procedure retrieves the user's name and adds text to the built-in document property (Comments), indicating that the user is logged on.

For each event procedure example to function properly, you need to add the following Windows application programming interface (API) declaration and custom function to the top of the class module where the document Save event procedure is defined for each application. Note the keyword Private added to the beginning of the GetUserName declaration. When you add a Windows API declaration in a class module, the keyword Private must appear in the declaration. If you add the API declaration in a standard code module, the Private keyword isn't required and you can use the API from any code module.

Private Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, _
        nSize As Long _
    ) As Long

Function CurrentUserName() As String
    Dim sBuffer As String * 25, lReturn As Long
    lReturn = GetUserName(sBuffer, 25)
    CurrentUserName = Left$(sBuffer, InStr(sBuffer, Chr(0)) - 1)
End Function

In all three examples below, the custom CurrentUserName function is called and its return value is assigned to the string variable sUserName. If the length of the string returned by the custom CurrentUserName function is greater than zero, the procedure uses the user's name in a text string assigned to the built-in Comments document property. If the length of the string is zero, the Windows API GetUserName returned an empty string, indicating that the user isn't logged onto the computer system. You use the BuiltInDocumentProperties property of the Document, Workbook, and Presentation objects in Word, Excel, and PowerPoint, respectively, to access the Comments document property.

In Word and Excel, rather than assigning the string "Last saved by user not logged on" to the Comments document property, you can display a message indicating to the user that the document or workbook can't be saved unless the user is logged on. After the message box is displayed, the Cancel Boolean argument passed as the third argument in the event procedure is set to True, therefore preventing the save action from being processed by Word or Excel. If you want to prevent the save action, replace the Else statement in the following event procedures for Word and Excel with the following Else statement:

  Else
     MsgBox "This document cannot be saved unless " & _
         "you are logged on."
     Cancel = True

The example scenario described in the document Close event procedures sets the Cancel argument to True when the Subject document property isn't set.

Set Up the DocumentBeforeSave 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 Document- BeforeSave from the Procedures drop-down list in the class module. In the DocumentBeforeSave event procedure, add the following code:

    3. Sub App_DocumentBeforeSave(ByVal Doc As Document, _
    SaveAsUI As Boolean, Cancel As Boolean)

    Dim sUserName As String
    sUserName = CurrentUserName
    If Len(sUserName) Then
        Doc.BuiltinDocumentProperties("Comments").Value = _
            "Last saved by user logged on as " & sUserName
    Else
        Doc.BuiltinDocumentProperties("Comments").Value = _
            "Last saved by user not logged on."
    End If
End Sub

  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 New on the Standard toolbar to create a new document.

  5. Add some text to the document and click the Save button on the Standard toolbar.

  6. To view the comments added to the document properties collection, display the Properties window of the saved file by clicking Properties on the File menu and selecting the Summary tab.

Evaluate and Cancel the Document Save Event Based on the Filename

  1. Replace the code in the DocumentBeforeSave event procedure added in step 2 above with the following code:

    2. Sub App_DocumentBeforeSave(ByVal Doc As Document, _
    SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim dlgSaveAs As Dialog, sFileName As String
    SaveAsUI = False
    Cancel = True

    Set dlgSaveAs = Dialogs(wdDialogFileSaveAs)
    dlgSaveAs.Display
    sFileName = dlgSaveAs.Name

    If Left$(sFileName, 4) = "http" Then
        If Len(Doc.BuiltInDocumentProperties("Subject") _
               .Value) = 0 Then
            MsgBox "Please enter a document subject " & _
                "before saving to a Web Folder.", _
                vbCritical
            Exit Sub
        End If
    End If
    dlgSaveAs.Execute
End Sub

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

  3. Switch to Word and click Save As on the File menu.

    4. In the DocumentBeforeSave event, the SaveAsUI Boolean argument passed into the DocumentBeforeSave event procedure is set to False so that your code can explicitly display the Save As dialog box. Because the SaveAsUI Boolean argument is set to False, Word will not display the Save As dialog box after the Save event procedure is executed. The Cancel Boolean argument passed into the event procedure is set to True in order to cancel Word's default behavior of saving the document. Your code will use the Execute method of the Save As dialog box to explicitly save the document. The Save As dialog box is displayed using the Display method of the Dialog object.

    After you select a file and click Save, the string value of the filename returned by the dialog box is evaluated. If the filename consists of a path to a Web Folder, the code evaluates if the user added text to the built-in document property Subject in the Properties dialog. If the Subject document property is not set, a message box is displayed and the Save event procedure is exited without saving the document. Otherwise, the save function of the Save As dialog box is executed using the Execute method of the Dialog object.

Set Up the WorkbookBeforeSave Event in Excel

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 Workbook-BeforeSave from the Procedures drop-down list in the class module. In the WorkbookBeforeSave event procedure, add the following code:

    3. Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
    ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim sUserName As String
    sUserName = CurrentUserName
    If Len(sUserName) Then
        Wb.BuiltinDocumentProperties("Comments").Value = _
            "Last saved by user logged on as " & sUserName
    Else
        Wb.BuiltinDocumentProperties("Comments").Value = _
            "Last saved by user not logged on."
    End If
End Sub

  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 then press F5.

  4. Switch to Excel and click New on the Standard toolbar to create a new workbook.

  5. Add some text to the cells in the workbooks and click the Save button on the Standard toolbar.

  6. To view the comments added to the document properties collection, display the Properties window of the saved file by clicking Properties on the File menu and selecting the Summary tab.

Set Up the PresentationSave Event in PowerPoint

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 PresentationSave from the Procedures drop-down list in the class module. In the PresentationSave event procedure, add the following code:

    3. Private Sub App_PresentationSave(ByVal Pres As Presentation)
    Dim sUserName As String
    sUserName = CurrentUserName
    If Len(sUserName) Then
        Pres.BuiltinDocumentProperties("Comments").Value = _
            "Last saved by user logged on as " & sUserName
    Else
        Pres.BuiltinDocumentProperties("Comments").Value = _
            "Last saved by user not logged on."
    End If
End Sub

  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 then press F5.

  4. Switch to PowerPoint and click New on the Standard toolbar to create a new presentation.

  5. Add some text to the presentation and click the Save button on the Standard toolbar.

  6. To view the comments added to the document properties collection, display the Properties window of the saved file by clicking Properties on the File menu and selecting the Summary tab.

NOTE
If PowerPoint is in its sequence of closing a presentation and if changes to the presentation will be saved, the PresentationSave event procedure is called after you close the presentation's document window. The presentation, however, is still in memory and can be manipulated. Attempting to change any aspect of the presentation's document window will result in an error.