Self deleting CommandBarButton possible?
 

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.

Self deleting CommandBarButton possible?
 

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." wrote in message
...
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.

Self deleting CommandBarButton possible?
 

So simple... Thanks a lot!!

"Bob Phillips" wrote in message
...
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." wrote in message
...
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.