Deleting a button

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!
--
www.brightfuture.ca/bright
My email address can be found on my site.

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


"Johnny Bright" <[email protected]> wrote in message
news:[email protected]...
> 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!
> --
> www.brightfuture.ca/bright
> My email address can be found on my site.

cbbGenerateReports.Delete

--
Regards,
Tom Ogilvy

"Johnny Bright" <[email protected]> wrote in message
news:[email protected]...
> 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!
> --
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> > --
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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!
> > > --
> > > www.brightfuture.ca/bright
> > > My email address can be found on my site.
> >
> >
> >