XL98: Some Worksheet Properties Not Preserved

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.

   Property            Default Value
   ----------------------------------------

   EnableSelection     0 - xlNoRestrictions
   EnableOutlining     False
   EnableAutoFilter    False
   EnablePivotTable    False
   EnableCalculation   True
   DisplayPageBreaks   False