Article ID: 169106
Article Last Modified on 10/22/2000
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q169106
SYMPTOMS
When you save, close, and then reopen a workbook in Microsoft Excel 97, 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 examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.
If you want to set the properties of a worksheet to specific values when you open a workbook, use a Visual Basic for Applications macro to set the properties when the workbook that contains the worksheet is opened. The following example 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 as 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, use the following steps:
- Switch to the Visual Basic Editor by pressing ALT+F11.
- In the Project window, click the worksheet with the properties you want to change.
- If the Properties window is not visible, click Properties Window on the View menu.
- In the Properties window, double-click the property you want to change, type the new value, and then press ENTER.
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."
NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Visual Basic Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:
120802 Office: How to Add/Remove a Single Office Program or Component
Additional query words: XL97 xlunlockedcells
Keywords: kbprb KB169106
