Macros to Alter Workspace Based on Active File (132509)

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