Self deleting CommandBarButton possible?

[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.

 

[Bob Phillips]

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)

 

[Bruno G.]

So simple... Thanks a lot!!

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)