Toggling Toolbar Buttons

You can place checkmarks next to your toolbar buttons similar to the Formula and Status Bar controls under the View menu. You simply need to set the State property of the CommandBarButton object. With Formula and Status Bar, you are either viewing them or not, i.e. they are simple toggles. You may, however, want to show the selected control among a number of control options.

This example shows a custom CommandBar with a popup control that holds three buttons. All of the buttons are assigned to the same macro. The procedure will change the State property of all of the buttons so that the selected button shows a check mark.

Checkmarkcb1

The code to change the State looks like this:

Sub DoToggle()

    Dim ctlButton As CommandBarButton
    Dim ctlPressed As CommandBarButton
    
    ‘Get the button that called the macro
    Set ctlPressed = Application.CommandBars.ActionControl
    
    ‘Loop through the controls changing the state of the
    ‘one that was pressed
    For Each ctlButton In ctlPressed.Parent.Controls
        If ctlButton.Index = ctlPressed.Index Then
            ctlButton.State = msoButtonDown
        Else
            ctlButton.State = msoButtonUp
        End If
    Next ctlButton
    
End Sub

This code doesn’t actually do anything useful, it just changes the State of the button that was selected. You would need to add your code to handle whatever it is you’re toggling within the If block.

If you’re intersted, here’s the code I used to create the controls. The commandbar I created manually.

Sub MakePu()

    With Application.CommandBars(“Custom 1?)
        On Error Resume Next
            .Controls(“MyToggle”).Delete
        On Error GoTo 0
        
        With .Controls.Add(msoControlPopup)
            With .Controls.Add(msoControlButton)
                .Caption = “Toggle 1?
                .OnAction = “DoToggle”
            End With
            
            With .Controls.Add(msoControlButton)
                .Caption = “Toggle 2?
                .OnAction = “DoToggle”
            End With
            
            With .Controls.Add(msoControlButton)
                .Caption = “Toggle 3?
                .OnAction = “DoToggle”
            End With
        
            .Caption = “MyToggle”
            
        End With
    End With
                
End Sub

Posted in Uncategorized

2 thoughts on “Toggling Toolbar Buttons

  1. I did a quick search on your site and found this one. I was able to put it into a project that I’m working on right now with very little modifications. Thanks so much for this one, it just saved me a bunch of time!

  2. Hi!
    I am tying to toogle a Menu button. I need the button to be check and unchecked.
    I try to use this code with a few changes but it didnt work..
    I dont know maybe I am misssing something here..
    Thank you!

    The english is not may native language so I apologyze If I made a big mistake here…


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.