Deleting a button

[Guest]

Hi there.

I have a public variable called cbbGenerateReports as commandBarButton.
I then run the following code which puts the button on the toolbar very
nicely but I want to delete the button on close since everytime I open this
workbook, it adds the button again such that I end up with the same button
there several times!

Private Sub Workbook_Open()

Set cbbGenerateReports = _
Application.CommandBars("Tools").Controls.Add(msoControlButton)

End Sub

I tried this but it didn't work on the BeforeClose event of the workbook.

Set cbbGenerateReports = _
Application.CommandBars("Tools").Controls.Item.Delete(msoControlButton)

Any ideas?

Thanks!

 

[PO]

Hi Johnny

Omit the Set statement:

Application.CommandBars("Tools").Controls.Item.Delete(msoControlButton)

NOT

Set cbbGenerateReports = _
Application.CommandBars("Tools").Controls.Item.Delete(msoControlButton)


If it still doesn't work try to place
Application.CommandBars("Tools").Controls.Item.Delete(msoControlButton)
in a public sub in a public module:

Sub RemoveControl()
Application.CommandBars("Tools").Controls.Item.Delete(msoControlButton)
End Sub

and call it from the Workbook_BeforeClose sub:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveControl
End Sub

Hope this helps.

Regards
po

 

[Tom Ogilvy]

cbbGenerateReports.Delete

 

[Guest]

For some reason Excel wanted this to be within a With.. statement, but it
worked. Thanks!
I didn't try PO's suggestion because I'm lazy but it looks good too! Thanks
to both!

John

cbbGenerateReports.Delete

 

[Tom Ogilvy]

I didn't try PO's suggestion because I'm lazy but it looks good too!
Thanks
to both!

Not to me.

--
Regards,
Tom Ogilvy

For some reason Excel wanted this to be within a With.. statement, but it
worked. Thanks!
I didn't try PO's suggestion because I'm lazy but it looks good too! Thanks
to both!

John