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.
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
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.
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
Oops! The line [ MsgBox TypeName(btnCaller.Caption) ], can be deleted… I was using that for testing purposes.
what is the point of this button?!?!?!?!?!
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.
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
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
“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!