XL: Macros to Alter Workspace Based on Active File
ID: Q132509
|
The information in this article applies to:
-
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 professionals 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/overview/overview.asp
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
Keywords : kbprg kbdta kbdtacode xlui PgmHowto KbVBA
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbhowto