Self Deleting Commandbar Buttons

William has a requirement to delete a menu item when it’s clicked. If you try to do this in a straight forward way, you’ll get a Method ‘Delete’ of object ‘_CommandBarButton’ failed because you can’t delete the button in the same procedure that was called by the button. The best way I could think to solve this was to tag the button for deletion and run another procedure an instant later that did the dirty business.

The first sub creates a button under the Tools menu which runs the DeleteMyself sub. When the button is clicked, the Tag property is changed and DeleteLater is scheduled to run right after execution of the current procedure is complete. DeleteLater find the control with the special tag and deletes it.

Sub CreateMenu()
   
    With Application.CommandBars(1).Controls(“Tools”)
        With .Controls.Add(msoControlButton)
            .Caption = “Clicktodelete”
            .OnAction = “DeleteMyself”
            .Visible = True
        End With
    End With
   
End Sub
 
Sub DeleteMyself()
   
    With Application.CommandBars(1).Controls(“Tools”)
        .Controls(“Clicktodelete”).Tag = “DELME”
    End With
   
    Application.OnTime Now, “DeleteLater”
   
End Sub
 
Sub DeleteLater()
   
    Dim Cbc As CommandBarButton
   
    Set Cbc = Application.CommandBars.FindControl(, , “DELME”)
   
    If Not Cbc Is Nothing Then
        Cbc.Delete
    End If
   
End Sub
Posted in Uncategorized

8 thoughts on “Self Deleting Commandbar Buttons

  1. Set Cbc = Application.CommandBars.FindControl(, , “DELME”)

    All these years, I didn’t know I could use the control’s tag as an argument in FindControl. That’s going to save a lot of looping.

  2. Using a module level variable, here’s an alternative way to accomplish the same thing:

    Private btnCaller As CommandBarButton

    Sub CreateMenu()
    With Application.CommandBars(1).Controls(“Tools”)
    With .Controls.Add(msoControlButton)
    .Caption = “Clicktodelete”
    .OnAction = “DeleteMyself”
    .Visible = True
    End With
    End With
    End Sub

    Sub DeleteMyself()
    Set btnCaller = CommandBars.ActionControl
    Application.OnTime Now, “DeleteLater”
    End Sub

    Sub DeleteLater()
    MsgBox TypeName(btnCaller.Caption)
    btnCaller.Delete
    Set btnCaller = Nothing
    End Sub

  3. Oops! The line [ MsgBox TypeName(btnCaller.Caption) ], can be deleted… I was using that for testing purposes.

  4. The situation that gave rise to the discussion was a two-stage document processor. When stage 1 was complete, a button would get added to the menu with the file. The button would do the stage 2 process, then delete the button from the toolbar.

  5. On reading your explanation of the specific use, not sure this would apply. It creates a temporary toolbar and adds a button whose OnAction makes itself invisible.

    Sub test()
    Dim cbar As CommandBar
    Dim cbarcontrol As CommandBarControl
    Dim cbarbutton As CommandBarButton

    On Error Resume Next
    Application.CommandBars(“test”).Delete
    On Error GoTo 0

    Set cbar = Application.CommandBars.Add(Name:=”test”, temporary:=True)
    With cbar
    Set cbarcontrol = .Controls.Add()
    .Visible = True
    Set cbarbutton = cbarcontrol
    With cbarbutton
    .FaceId = 2
    .OnAction = “button_macro”
    End With
    ‘this is just a placeholder second button that shifts to the position of the first button
    Set cbarcontrol = .Controls.Add
    End With
    End Sub

    Sub button_macro()
    MsgBox “You could do something useful here”
    Application.CommandBars(“test”).Controls(1).Visible = False
    End Sub

  6. On my ride home I realized that this would be chance to use Actioncontrol, a property for which I always think I might have a use, but then don’t. Anyways, here’s a toolbar on which each button disappears when clicked. When all the buttons are gone the toolbar goes too. Useless, but fun – I love toolbar stuff (too bad about XL 12).

    Is there a way to make quotes work in these posts, so that when you pasted them back into the VBE they’re still recognized?

    Sub test()
    Dim cbar As CommandBar
    Dim cbarcontrol As CommandBarControl
    Dim cbarbutton As CommandBarButton
    Dim i As Long

    On Error Resume Next
    Application.CommandBars(“test”).Delete
    On Error GoTo 0

    Set cbar = Application.CommandBars.Add(Name:=”test”, temporary:=True)
    With cbar
    For i = 1 To 3
    Set cbarcontrol = .Controls.Add()
    .Visible = True
    Set cbarbutton = cbarcontrol
    With cbarbutton
    .Style = msoButtonIconAndCaption
    .FaceId = i + 1
    .Caption = i
    .OnAction = “button_macro”
    End With
    Next i
    End With
    End Sub

    Sub button_macro()
    Dim ctl As CommandBarControl
    Dim controls_remaining As Boolean

    With CommandBars.ActionControl
    .Visible = False
    With .Parent
    For Each ctl In .Controls
    If ctl.Visible Then
    controls_remaining = True
    End If
    Next ctl
    If Not controls_remaining Then
    .Visible = False
    End If
    End With
    End With
    End Sub

  7. “I realized that this would be chance to use Actioncontrol”

    Havn’t seen that before Doug. Good one.

    “The situation that gave rise to the discussion was a two-stage document processor. When stage 1 was complete, a button would get added to the menu with the file. The button would do the stage 2 process, then delete the button from the toolbar.”

    Hummm? I think I would use enable. If the toolbar is there, then i think all the options should be visable, that way when the document is ready and the botton is enabled the user can see that “in state A I can’t do X” and that “in state B i can do X”

    Just my 2 cents!


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

Leave a Reply

Your email address will not be published.