Microsoft KB Archive/109064

= Visual Basic Macro to Hide and Restore All Toolbars =

Article ID: 109064

Article Last Modified on 6/11/2007

-

APPLIES TO


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

-



This article was previously published under Q109064





SUMMARY
In Microsoft Excel, the following Microsoft Visual Basic for Applications code will show all, hide all, or toggle the Visible property of the displayed toolbars.



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.

Sample Visual Basic Procedures
' This is a Sub procedure to hide all of the toolbars.

Sub HideAllToolbars ' Dimension a loop variable. Dim i As Integer

' Loop through the total number of toolbars. For i = 1 To Application.Toolbars.Count

' Hide each toolbar. Application.Toolbars(i).Visible = False

' End of loop. Next i

End Sub

' This is a sub-routine to show all of the toolbars.

Sub ShowAllToolbars 'loop variable Dim i As Integer

' Loop through the total number of toolbars. For i = 1 To Application.Toolbars.Count

' Show each toolbar. Application.Toolbars(i).Visible = True

' End of loop. Next i

End Sub

' The following routine when run the first time will store all of the ' toolbars visible property then hide all of the visible toolbars. When ' the routine is run a second time, it will restore the toolbars to  ' their original state the first time the code was executed.

' This Sub procedure will toggle all currently visible toolbars to  ' hidden and when rerun will restore the toolbars.

Sub ToggleToolbars ' Creates a 20 element array to keep track of current toolbar ' settings on the first iteration through the routine. ' This limits this routine to 20 toolbars total (increasing this      ' number will allow for more custom toolbars). ' In Microsoft Excel 97 or Microsoft Excel 98, it is recommended ' that you use a value of at least 80.

Static CurrentToolSet(20) As Boolean Static Flag As Boolean Dim i As Integer

' If this is the first time through the routine, do this... If Flag = False Then

' Loop through all of the toolbars. For i = 1 To Application.Toolbars.Count

' Store the visible property of each toolbar in the array ' CurrentToolSet. CurrentToolSet(i) = Application.Toolbars(i).Visible ' Hide all of the toolbars. Application.Toolbars(i).Visible = False

' End of loop. Next i

' Set flag to true to skip this section next time through. Flag = True Else

' Loop through all of the toolbars. For i = 1 To Application.Toolbars.Count

' Restore toolbar setting to the original value as saved in              ' the array. Application.Toolbars(i).Visible = CurrentToolSet(i)

' End of loop. Next i

' Set flag back to false to hide visible toolbars on the next ' time this is run. Flag = False

' End of block if statement. End If

End Sub

Additional query words: 5.00a 5.00c 8.00 XL97 XL98 tool bar example sample commandbars XL

Keywords: kbdtacode kbhowto kbprogramming KB109064

-

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

© Microsoft Corporation. All rights reserved.