comandbar controls

[RobcPettit]

Hi, Ive biult a tool bar with commandbar controls. If I click one of
my command buttons, it fires the onaction and runs my macro. I want my
macro to delete this command button and a couple of others. I can
delete he others ok, but not the one clicked. Im using 'Set cbct1 =
CommandBars("Customer").FindControl(Tag:="Six")
cbct1.Delete' for each one. Ive been trying to deselect the button,
but cant work out how. Ive tried enabling and disabling, but this
didnt work. Any ideas
Regards Robert

 

[Peter T]

You'll need an OnTime macro for that. Get back if not sure how.

Regards,
Peter T

 

[Chip Pearson]

You can't delete the control while it is in use. You can use the
OnTime method to tell Excel to run a procedure as soon as it is done
with other requests. E.g.,

Option Explicit

Private pDeleteControl As Office.CommandBarControl

Sub CreateTheControl()
''''''''''''''''''''''
' create the control.
''''''''''''''''''''''
Dim C As Office.CommandBarButton
Set C = Application.CommandBars.ActiveMenuBar. _
Controls("Tools").Controls.Add(Type:=msoControlButton, _
temporary:=True)
With C
.Caption = "Click me"
.OnAction = "'" & ThisWorkbook.Name & "'!ClickProc"
.Tag = "TheTag"
End With
End Sub

Sub ClickProc()
'''''''''''''''''''''''''''
' called by OnAction.
' use OnTime to schedule
' the control for deletion.
'''''''''''''''''''''''''''
MsgBox "Clicked"
Set pDeleteControl = _
Application.CommandBars.FindControl(Tag:="TheTag")
Application.OnTime Now, "DeleteIt", , True
End Sub

Sub DeleteIt()
''''''''''''''''''''
' delete the control.
' called by OnTime.
''''''''''''''''''''
If Not pDeleteControl Is Nothing Then
pDeleteControl.Delete
Set pDeleteControl = Nothing
End If
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RobcPettit]

Thankyou for your reply. Is this were I set the macro to fire at a
certain time. If so how does it work with deleting the button. Im
thinking that on clicking the button, the ontime method is set, and
the button is released, because as far as the buttons concerned its
finished its task.
Regards Robert

 

[RobcPettit]

Thankyou, thats makes sense.
Regards Robert

 

[Peter T]

Chip has provided a complete demo. Looking at the close timings of your
posts his may not have been visible to you.

Regards,
Peter T