Microsoft KB Archive/132509

= Macros to Alter Workspace Based on Active File =

Article ID: 132509

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q132509



SUMMARY
When you create a custom project in Microsoft Visual Basic for Applications, you may want to alter options in the user environment in such a way that when the user activates your project file, their environment is altered, but when the user activates another file, their environment is restored to the defaults you choose and the toolbars they had visible initially. This article contains an example of the type of code necessary to do this.



MORE INFORMATION
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. This set of Sub procedures must work together to be effective. The Run_Me_First procedure must be run first.

NOTE: The Run_Me_First procedure hides the sheet tabs in your workbook. Therefore, after you run this code, you must press CTRL+PAGE UP or CTRL+PAGE DOWN to move through the sheets in your workbook.

Note that the sample code in this article is a simple subset of the possibilities available to you (you may want to enhance this set of environment alterations). ' Dimension a module level variable to preserve the array of visible ' toolbars generated by the Run_Me_First Sub procedure. Dim ToolArray As String

Sub Run_Me_First

' When you activate another sheet in this workbook after you run ' this code, you must use the CTRL+PAGE UP or CTRL+PAGE DOWN key ' combinations to access other sheets.

' Dimension variable as stand-in for worksheets. Dim osheet As Object

' Dimensions variable as a counter for the toolbar loop. Dim tcounter As Integer

Application.ScreenUpdating = False

' Loop through all the worksheets in the this workbook. For Each osheet In ThisWorkbook.Worksheets

' NOTE: ' In order to disable this property, you must set the ' OnSheetActivate and OnSheetDeactivate properties to "" i.e.: '          '    osheet.OnSheetActivate = ""

' Assign Setup_Environment macro to run when worksheet is          ' activated. osheet.OnSheetActivate = "Setup_Environment" ' Assign Restore_Environment macro to run when worksheet is          ' deactivated. osheet.OnSheetDeactivate = "Restore_Environment" ' Loop back. Next osheet ' Loop through all the toolbars known to your current installation ' of Microsoft Excel. For Each t In Toolbars If t.Visible = True Then ' If the toolbar is showing now ' increment the toolbar counting variable by 1. tcounter = tcounter + 1 ' Redimension the toolarray variable to hold as many ' elements as the toolcounter variable is now indicating ' are visible. ReDim Preserve ToolArray(1 To tcounter) ' Populate this position in the array with the name of the ' visible toolbar. ToolArray(tcounter) = t.Name ' End the conditional branch started by the block if              ' statement. End If      ' Loop back to take a look at the next toolbar known to Excel. Next t  End Sub

' This Sub is run by activating a worksheet in this workbook after ' running the Run_Me_First Sub procedure.

Sub Setup_Environment

Application.ScreenUpdating = False

With Application .DisplayStatusBar = False 'turn off the status bar .DisplayFormulaBar = False 'turn off the formula bar .DisplayScrollBars = False 'turn off the scroll bars End With

' Create an error handler in case the active window isn't on a      ' worksheet. On Error Resume Next ' Turn off gridlines. ActiveWindow.DisplayGridlines = False ' Turn off row and column headings. ActiveWindow.DisplayHeadings = False Dim scounter As Integer 'dimension an integer variable ' Loop the following lines as many times as there are items in the ' ToolArray variable. For scounter = 1 To UBound(ToolArray) ' Hide the toolbars named in the ToolArray variable. Toolbars(ToolArray(scounter)).Visible = False Next scounter ' Loop.

End Sub

' This Sub is run by deactivating a worksheet in this workbook after ' running the Run_Me_First Sub procedure.

Sub Restore_Environment

Application.ScreenUpdating = False

With Application .DisplayStatusBar = True  ' Turn on the status bar. .DisplayFormulaBar = True ' Turn on the formula bar. .DisplayScrollBars = True ' Turn on the scroll bars. End With ' Create an error handler in case the active window isn't on a      ' worksheet. On Error Resume Next ' Turn on gridlines. ActiveWindow.DisplayGridlines = True ' Turn on row and column headings. ActiveWindow.DisplayHeadings = True Dim rcounter As Integer ' Dimension an integer variable. ' Loop the following lines as many times as there are items in the ' ToolArray variable. For rcounter = 1 To UBound(ToolArray) ' Show the toolbars named in the ToolArray variable. Toolbars(ToolArray(rcounter)).Visible = True Next rcounter  ' Loop. End Sub

Additional query words: 5.00a 5.00c 8.00 XL98 XL97 XL7 XL5 mac configure customize environment display XL

Keywords: kbhowto kbprogramming kbdtacode KB132509

-

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

© Microsoft Corporation. All rights reserved.