XL: Visual Basic Macro to Hide and Restore All Toolbars

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

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, 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

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