Change the availability for the CommandBars using VBA in Microsoft Excel

With the macro below you can toggle the availability of the Workbook Menu Bar,the Standard toolbar and a custom CommandBar.
The first time you run the macro the menu and Standard bar are disabled, the custom toolbar is enabled.
The next time you run the macro the menu and the Standard bar are enabled, the custom CommandBar is disabled.

Sub ToggleCommandBars()
Dim cbEnabled As Boolean
    ' get the current commandbar state
    cbEnabled = Not Application.CommandBars(1).Enabled
    ' apply the new state to the Workbook Menu Bar
    Application.CommandBars(1).Enabled = cbEnabled
    ' apply the new state to the Standard toolbar
    Application.CommandBars("StandardOPE").Enabled = cbEnabled
    ' apply the new state to a custom commandbar (the oposite of the previous two)
    Application.CommandBars("MyCustomCommandBar").Enabled = Not cbEnabled
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube