toolbar won't go away

[sugargenius]

I'm trying to add/remove command bar in code. It is created fine, but
persists after workbook is closed.

In ThisWorkbook code I have:

Sub Workbook_Open()
Call BuildToolBar
End Sub
Sub Workbook_Close()
Call RemoveToolBar
End Sub

In code module, I have:

Sub BuildToolBar()
Dim cb As CommandBar
Dim cbcCommandBarButton1 As CommandBarButton
Dim cbcCommandBarButton2 As CommandBarButton

'delete commandbar if it exists
On Error Resume Next
Application.CommandBars.Item("Prep Actual Costs").Delete
On Error GoTo 0

'create new commandbar
Set cb = Application.CommandBars.Add(Name:="Prep Actual Costs",
Position:=msoBarTop)
Set cbcCommandBarButton1 = cb.Controls.Add(Type:=msoControlButton)
With cbcCommandBarButton1
.Caption = "&Prep Actuals"
.OnAction = "'" & ThisWorkbook.Name & "'!PrepActuals"
.FaceId = 2950
.Style = msoButtonIconAndCaption
End With

Set cbcCommandBarButton2 = cb.Controls.Add(Type:=msoControlButton)
With cbcCommandBarButton2
.Caption = "&Show Labor Lookup"
.OnAction = "'" & ThisWorkbook.Name & "'!ShowLaborLookup"
.FaceId = 19
.Style = msoButtonIconAndCaption
End With

cb.Visible = True
End Sub
Sub RemoveToolBar()
On Error Resume Next
Application.CommandBars("Prep Actual Costs").Delete
End Sub

[David Adamson]

Just try putting the code in the Auto_Close Event

Sub Auto_Close()
On Error Resume Next
CommandBars("Prep Actual Costs").Delete
End sub

[sugargenius]

I'd already put it in Workbook_BeforeClose, and that fixed the problem.
Is Auto_Close more appropriate?

[David Adamson]

Sorry can't help you there about appropriate or best

As I only have the basics, it's just how I do it and it works so I keep
doing it

"sugargenius" <sugargenius@yahoo.com> wrote in message
news:1131929185.413143.76160@f14g2000cwb.googlegroups.com...
> I'd already put it in Workbook_BeforeClose, and that fixed the problem.
> Is Auto_Close more appropriate?
>

[sugargenius]

I don't have to worry about the distinction. Auto_Close didn't work.

[Tom Ogilvy]

In the ThisWorkbook, Userform and Sheet modules, you would be advised to
select your event procedures from the dropdowns at the top of the module.
This will eliminate misnaming your procedures as you show you have done.

The Auto_Close macro, in a general module was used to perform this function
in xl95 and xl5. The newer generation of events was added in xl97.

Probably better to use the BeforeClose Event.

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"sugargenius" <sugargenius@yahoo.com> wrote in message
news:1131929185.413143.76160@f14g2000cwb.googlegroups.com...
> I'd already put it in Workbook_BeforeClose, and that fixed the problem.
> Is Auto_Close more appropriate?
>

[Tom Ogilvy]

Auto_close has to be in a general module, not the ThisWorkbook module.

--
Regards,
Tom Ogilvy

"sugargenius" <sugargenius@yahoo.com> wrote in message
news:1131930202.465510.312760@z14g2000cwz.googlegroups.com...
> I don't have to worry about the distinction. Auto_Close didn't work.
>

[David Adamson]

Thanks for the clariffication


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uzQBjgL6FHA.2524@TK2MSFTNGP10.phx.gbl...
> In the ThisWorkbook, Userform and Sheet modules, you would be advised to
> select your event procedures from the dropdowns at the top of the module.
> This will eliminate misnaming your procedures as you show you have done.
>
> The Auto_Close macro, in a general module was used to perform this
> function
> in xl95 and xl5. The newer generation of events was added in xl97.
>
> Probably better to use the BeforeClose Event.
>
> http://www.cpearson.com/excel/events.htm
>
> --
> Regards,
> Tom Ogilvy
>

[sugargenius]

I'd never noticed that. Thanks

[Macro Beginner]

When you add the commandbar, specify the "Temporary" option to "True", then
it will be deleted automatically when Excel was closed.

Application.CommandBars.Add(Temporary:=True)

Hope this helps.

"sugargenius" wrote:

> I'm trying to add/remove command bar in code. It is created fine, but
> persists after workbook is closed.
>
> In ThisWorkbook code I have:
>
> Sub Workbook_Open()
> Call BuildToolBar
> End Sub
> Sub Workbook_Close()
> Call RemoveToolBar
> End Sub
>
> In code module, I have:
>
> Sub BuildToolBar()
> Dim cb As CommandBar
> Dim cbcCommandBarButton1 As CommandBarButton
> Dim cbcCommandBarButton2 As CommandBarButton
>
> 'delete commandbar if it exists
> On Error Resume Next
> Application.CommandBars.Item("Prep Actual Costs").Delete
> On Error GoTo 0
>
> 'create new commandbar
> Set cb = Application.CommandBars.Add(Name:="Prep Actual Costs",
> Position:=msoBarTop)
> Set cbcCommandBarButton1 = cb.Controls.Add(Type:=msoControlButton)
> With cbcCommandBarButton1
> .Caption = "&Prep Actuals"
> .OnAction = "'" & ThisWorkbook.Name & "'!PrepActuals"
> .FaceId = 2950
> .Style = msoButtonIconAndCaption
> End With
>
> Set cbcCommandBarButton2 = cb.Controls.Add(Type:=msoControlButton)
> With cbcCommandBarButton2
> .Caption = "&Show Labor Lookup"
> .OnAction = "'" & ThisWorkbook.Name & "'!ShowLaborLookup"
> .FaceId = 19
> .Style = msoButtonIconAndCaption
> End With
>
> cb.Visible = True
> End Sub
> Sub RemoveToolBar()
> On Error Resume Next
> Application.CommandBars("Prep Actual Costs").Delete
> End Sub
>
>