Microsoft KB Archive/213487

= XL2000: Visual Basic for Applications Macro to Hide and Restore All Toolbars =

Article ID: 213487

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213487





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



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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Sample Visual Basic for Applications 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: tool bar example sample commandbars XL2000

Keywords: kbdtacode kbhowto kbprogramming KB213487

-

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

© Microsoft Corporation. All rights reserved.