Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Some of the properties of worksheets are not preserved in Excel


View products that this article applies to.

This article was previously published under Q211809
For a Microsoft Excel 97 version of this article, see 169106 (http://support.microsoft.com/kb/169106/ ) .
For a Microsoft Excel 97 version of this article, see 169106 (http://support.microsoft.com/kb/169106/ ) .

↑ Back to the top


Symptoms

When you save, close, and then reopen a workbook in Microsoft Excel, some of the properties of worksheets in the workbook are not saved. The following properties are affected:
  • EnableAutoFilter
  • EnableCalculation
  • EnableOutlining
  • DisplayPageBreaks
  • EnablePivotTable
  • EnableSelection

↑ Back to the top


Cause

This issue occurs because these worksheet properties are not designed to be saved when you close a workbook.

↑ Back to the top


Workaround

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 a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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 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 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.

↑ Back to the top


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 ALT+F11.
  2. In the Project window, click the worksheet with the 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, click the property you want to change and select the value you want from the drop down box to the right.

↑ Back to the top


Keywords: KB211809, kbprb, kbexpertisebeginner

↑ Back to the top

Article Info
Article ID : 211809
Revision : 9
Created on : 4/2/2007
Published on : 4/2/2007
Exists online : False
Views : 385