XL: Macros to Alter Workspace Based on Active File

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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, 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 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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