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

Just try putting the code in the Auto_Close Event

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

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

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" wrote in message
ups.com...
I'd already put it in Workbook_BeforeClose, and that fixed the problem.
Is Auto_Close more appropriate?

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

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" wrote in message
ups.com...
I'd already put it in Workbook_BeforeClose, and that fixed the problem.
Is Auto_Close more appropriate?

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

--
Regards,
Tom Ogilvy

"sugargenius" wrote in message
oups.com...
I don't have to worry about the distinction. Auto_Close didn't work.

Thanks for the clariffication


"Tom Ogilvy" wrote in message
...
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

I'd never noticed that. Thanks

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