XL2000: Visual Basic for Applications Macro to Hide and Restore All Toolbars (213487)

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: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

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