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(msoC ontrolButton)

End Sub

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

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

Any ideas?

Thanks!
--
www.brightfuture.ca/bright
My email address can be found on my site.

Hi Johnny

Omit the Set statement:

Application.CommandBars("Tools").Controls.Item.Del ete(msoControlButton)

NOT

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


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

Sub RemoveControl()
Application.CommandBars("Tools").Controls.Item.Del ete(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


"Johnny Bright" wrote in message
...
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(msoC ontrolButton)

End Sub

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

Set cbbGenerateReports = _

Application.CommandBars("Tools").Controls.Item.Del ete(msoControlButton)

Any ideas?

Thanks!
--
www.brightfuture.ca/bright
My email address can be found on my site.

cbbGenerateReports.Delete

--
Regards,
Tom Ogilvy

"Johnny Bright" wrote in message
...
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(msoC ontrolButton)

End Sub

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

Set cbbGenerateReports = _

Application.CommandBars("Tools").Controls.Item.Del ete(msoControlButton)

Any ideas?

Thanks!
--
www.brightfuture.ca/bright
My email address can be found on my site.

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

"Tom Ogilvy" wrote:

cbbGenerateReports.Delete

--
Regards,
Tom Ogilvy

"Johnny Bright" wrote in message
...
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(msoC ontrolButton)

End Sub

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

Set cbbGenerateReports = _

Application.CommandBars("Tools").Controls.Item.Del ete(msoControlButton)

Any ideas?

Thanks!
--
www.brightfuture.ca/bright
My email address can be found on my site.

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

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

"Tom Ogilvy" wrote:

cbbGenerateReports.Delete

--
Regards,
Tom Ogilvy

"Johnny Bright" wrote in
message
...
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(msoC ontrolButton)

End Sub

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

Set cbbGenerateReports = _

Application.CommandBars("Tools").Controls.Item.Del ete(msoControlButton)

Any ideas?

Thanks!
--
www.brightfuture.ca/bright
My email address can be found on my site.