Self deleting CommandBarButton possible?

  • Thread starter Thread starter Bruno G.
  • Start date Start date
B

Bruno G.

Hello!

I'm using a customized version of John Walkenbach's Menu Maker
(http://j-walk.com/ss/excel/tips/tip53.htm) to create a toolbar when a
workbook is opened.

I would like to add a 'Refresh' button to this toolbar, which would cause
the toolbar to delete itself and be recreated. But when the sub tries to
delete the button that started it, it fails.

Is there a way around that?

Thanks
Bruno G.
 
Bruno,

Give this approach a try

Sub Refresh()
Application.OnTime Now + TimeValue("00:00:01"), "subRefresh"
End Sub

Sub subRefresh()
Dim oCB As CommandBar
Dim oCtl As CommandBarButton

Set oCB = Application.CommandBars("Formatting")
On Error Resume Next
oCB.Controls("Refresh").Delete
On Error GoTo 0

Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)
oCtl.Caption = "Refresh"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "Refresh"

Set oCtl = Nothing
Set oCB = Nothing

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
So simple... Thanks a lot!!

Bob Phillips said:
Bruno,

Give this approach a try

Sub Refresh()
Application.OnTime Now + TimeValue("00:00:01"), "subRefresh"
End Sub

Sub subRefresh()
Dim oCB As CommandBar
Dim oCtl As CommandBarButton

Set oCB = Application.CommandBars("Formatting")
On Error Resume Next
oCB.Controls("Refresh").Delete
On Error GoTo 0

Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)
oCtl.Caption = "Refresh"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "Refresh"

Set oCtl = Nothing
Set oCB = Nothing

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top