XL98: Some Worksheet Properties Not Preserved (178986)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q178986

SYMPTOMS

When you save, close, and then reopen a workbook in Microsoft Excel 98 Macintosh Edition, some of the properties of worksheets in the workbook may not be saved. The following properties are affected:

EnableAutoFilter
EnableCalculation
EnableOutlining
DisplayPageBreaks
EnablePivotTable
EnableSelection

CAUSE

These worksheet properties are not designed to be saved when you close a workbook.

WORKAROUND

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, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. If you want to set the properties of a worksheet to specific values when you open a workbook, use a Microsoft Visual Basic for Applications macro to set the properties when the workbook that contains the worksheet is opened. The following sample macro demonstrates one way to do this: 
   ' Auto_Open macros run automatically when you open the workbook.
   Sub Auto_Open()
 
       With ThisWorkbook.Worksheets("Sheet1")
 
           ' Set as many of these properties as you need.
           .EnableSelection = xlNoSelection
           .EnablePivotTable = True
           .EnableOutlining = True
           .EnableAutoFilter = True
           .DisplayPageBreaks = True
           .EnableCalculation = False
 
       End With

   End Sub
If you insert this code into a module in the workbook, the macro sets the properties for worksheet Sheet1 whenever you open the workbook manually.

NOTE: If you use a macro to open the workbook, you must use the RunAutoMacros method to run the Auto_Open macro.

MORE INFORMATION

The properties listed in this article are not preserved when you close a workbook. When you reopen the workbook, the properties for each worksheet revert to their default values, which are listed in the following table. 
   Property            Default Value
   ----------------------------------------

   EnableSelection     0 - xlNoRestrictions
   EnableOutlining     False
   EnableAutoFilter    False
   EnablePivotTable    False
   EnableCalculation   True
   DisplayPageBreaks   False
You can set these properties programmatically, as in the "Workaround" section in this article, or you can use the Properties window in the Visual Basic Editor to set them. To do this, follow these steps:
  1. Switch to the Visual Basic Editor by pressing OPTION+F11.
  2. In the Project window, click the worksheet whose properties you want to change.
  3. If the Properties window is not visible, click Properties Window on the View menu.
  4. In the Properties window, double-click the property you want to change, type the new value, and then press RETURN.

REFERENCES

For more information about properties listed in this article, click the Office Assistant in the Visual Basic Editor, type EnableSelection, click Search, and then click to view "EnableSelection Property".
Modification Type:MajorLast Reviewed:6/17/2005
Keywords:kbcode kbprb kbui KB178986
©2004 Microsoft Corporation. All rights reserved.