Error message when you use a VBA macro to save a shared Excel 2007 workbook: "Application-defined or object-defined error"

When you try to run a Microsoft Visual Basic for Applications (VBA) macro that saves the current workbook as a shared workbook in Microsoft Office Excel 2007, you receive the following error message:

This issue may occur if the following conditions are true:

• The default save format is set to Excel 97-2003. Additionally, a new workbook is added, and the macro uses the ProtectSharing method or the SaveAs method against the new workbook in the Excel 97-2003 file format.
• The ProtectSharing method uses the FileFormat property set to xlExcel8.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To work around this issue, use one of the following methods.

Method 1: Set the default file format to xlWorkbookNormal

To use either the ProtectSharing method or the SaveAs method in a VBA macro, set the default file format to ExcelWokbookNormal early in your VBA macro.

The following VBA code is an example of how to use the DefaultSaveFormat method to set the default file format to xlWorkbookNormal. Note To manually set the DefaultSaveFormat method, follow these steps:

1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Save.
3. In the Save files in this format box, click the format in which you want workbooks to be saved by default.
4. Click OK to close the Excel Options dialog box.

Method 2: Use the SaveAs method with the AccessMode parameter

To use the SaveAs method, use the AccessMode parameter.

The following VBA code is an example of how to use the SaveAs method with the AccessMode parameter. Note The value of the FileFormat property must be the same value as the DefaultSaveFormat method.