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]

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

 

[Tom Ogilvy]

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

 

[David Adamson]

Thanks for the clariffication

 

[sugargenius]

I'd never noticed that. Thanks

 

[Guest]

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.