toolbar won't go away

S

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
 
D

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
 
S

sugargenius

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

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
 
T

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
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top