XL98: OnSave Property Is Not Available in Object Browser

ID: Q184707

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SYMPTOMS

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

NOTE: This behavior is different from the behavior of the Object Browser in earlier versions of Microsoft Excel.

STATUS

This is by design in Microsoft Excel 98.

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 professionals 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/

The OnSave property has been replaced by the BeforeSave event in Microsoft Excel 98.

The OnSave property was introduced in Microsoft Excel version 5.0. However, you can still use it in Microsoft Excel 98. The OnSave property returns or sets the name of a Visual Basic procedure that is run after you invoke the Save or Save As command. Note that this procedure is run before the workbook is actually saved. When you use the OnSave property, consider the following:

The procedure that is specified to run with the OnSave property must accept one Boolean argument (see the example that follows).
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.
If you save the workbook when a macro or mail command is run or when an embedded workbook is updated, this event is not called.

Macro Example Using the OnSave Property

The following example displays a message box after you invoke the Save or Save As command. The message box appears before the workbook is 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

Run the SetSaveEvent macro. This sets an internal flag in Microsoft Excel to run the SaveProcedure macro automatically when the active workbook is saved.

Macro Example Using the BeforeSave Event

The BeforeSave event is executed before a workbook is saved. This event is new in Microsoft Excel 98.

The following example prompts you for a yes or a no response before it saves the workbook:

1. In the Project Explorer window of Visual Basic Editor, double-click

   ThisWorkbook in the current project.

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

2. In the Object list for this module, click Workbook.

3. In the Procedure list for 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 that

   prompts you 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, from the Visual Basic Editor, click the Office Assistant, type "BeforeSave," click Search, and then click to view "BeforeSave Event."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q176476
   TITLE     : OFF: Office Assistant Not Answering Visual Basic Questions

Additional query words: XL98

Keywords          : kbprg kbdta kbdtacode xlvbahowto xlvbainfo xlmac 
Version           : MACINTOSH:98
Platform          : MACINTOSH
Issue type        : kbprb

Last Reviewed: May 17, 1999