Microsoft KB Archive/116328

= Restoring Settings in Options Dialog After Running Macro =

Article ID: 116328

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q116328





SUMMARY
In Microsoft Excel, when you run a Visual Basic macro that makes changes to a setting in the Options dialog box, the setting may affect all workbooks. Therefore, the setting may not be restored, for example, when you close the workbook the macro was making changes to.

You can use the following sample code in your macro to store a setting in the Options dialog box that affects all workbooks and to restore the setting after your macro is run.

Note that this example does not include all settings in the Options dialog box. In addition, in your macro, you only need to include the individual settings that you know will be changed by your macro.



MORE INFORMATION
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. Sub Store_App_Options ' Dimension variables Dim Drag as Boolean, Move as Boolean, Dec As Boolean Dim Formula as Boolean, Status As Boolean Dim Notes as Boolean, Info as Boolean, Request As Boolean Dim Places As Integer Dim Ref As String

' Store Application Edit, View and General options With Application

' Edit options Drag = .CellDragAndDrop Move = .MoveAfterReturn Dec = .FixedDecimal Places = .FixedDecimalPlaces

' View options Formula = .DisplayFormulaBar Status = .DisplayStatusBar Notes = .DisplayNoteIndicator Info = .DisplayInfoWindow

' General options Ref = .ReferenceStyle Request = .IgnoreRemoteRequests End With

' Continue with the rest of your macro here

' Restore Application Edit, View and General options ' at the end of your macro With Application

' Edit options .CellDragAndDrop = Drag .MoveAfterReturn = Move .FixedDecimal = Dec .FixedDecimalPlaces = Places

' View options .DisplayFormulaBar = Formula .DisplayStatusBar = Status .DisplayNoteIndicator = Notes .DisplayInfoWindow = Info

' General options .ReferenceStyle = Ref .IgnoreRemoteRequests = Request

End With End Sub

Additional query words: 5.00a 5.00c 7.00a workspace XL

Keywords: kbprogramming KB116328

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.