XL97: OnSave Property Not Available in Object Browser

• Microsoft Excel 97 for Windows

SYMPTOM

In Visual Basic Editor for Microsoft Excel 97, there is no reference to the OnSave property in the Object Browser.

NOTE: This behavior differs from functionality of the Object Browser used in earlier versions of Microsoft Excel.

STATUS

This is by design in Microsoft Excel 97.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

The OnSave property was introduced in Microsoft Excel for Windows 95, version 7.0, but still can be used in Microsoft Excel 97. The OnSave property returns or sets the name of a Visual Basic procedure to run after the user invokes either the Save or Save As command, but before the workbook is actually saved. Consider the following items when you use the OnSave property:

• The procedure that is specified to run with the OnSave property must take one Boolean argument (see the example below).
• Only Visual Basic procedures are supported by this property; Microsoft Excel version 4.0 Macro Language procedures are not supported.
• The value of the OnSave property is not saved with the workbook; it must be reset each time the workbook is opened.
• This event is not called if the workbook is saved when a macro or mail command runs, or when an embedded workbook is updated.

Macro Example using the OnSave property

This example displays a message box after the user invokes either the Save or Save As command, but before the workbook is actually saved:

     'Specifies the procedure to run when the workbook is saved
     Sub SetSaveEvent()
         ActiveWorkbook.OnSave = "SaveProcedure"
     End Sub

     Sub SaveProcedure(s As Boolean)
         MsgBox "Microsoft Excel will now save your work."
     End Sub

Macro Example using the BeforeSave event

The BeforeSave event executes before a workbook is saved. This event is new to Microsoft Excel 97.

This example prompts the user for either a yes or no response before saving the workbook:

1. In the Project Explorer window of Visual Basic Editor, double-click ThisWorkbook in the current project.

   This opens a module for code that runs "behind" the workbook.

2. In the Object drop-down of this module, click Workbook.

3. In the Procedure drop-down of this module, click BeforeSave.

4. Enter the code so the Workbook_BeforeSave procedure resembles the following:

        Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
            Cancel As Boolean)
   
            a = MsgBox("Do you really want to save this workbook?", vbYesNo)
            If a = vbNo Then Cancel = True
   
        End Sub
   
   

5. On the File menu, click "Close and Return to Microsoft Excel."

6. If you click Save on the File menu, you receive a message box with a prompt asking if you really want to save the file. If you click Yes, your file is saved. If you click No, your file is not saved.

REFERENCES

For more information about the BeforeSave event, click the Index tab in Microsoft Visual Basic Help, type the following text

     BeforeSave