Limit Custom Toolbar to Workbook not all active workbooks

[Beeblebrox]

I've created a custom toolbar using VBA that works fine except that when I open the file with it in it and then open a different file, the toolbar shows up in both open files and properly goes away when I close the file it is in. I want to limit the toolbar to the one file where I placed the code. What do I need to do?

Here's what I did.

In thisworkbook

Private Sub WorkBook_Open()
Call AddButtons
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("NLV Printing ToolBar").Delete
On Error GoTo 0
End Sub

In a module

Option Explicit

Sub AddButtons()
Dim NewBtn As CommandBarControl
Dim TBar As CommandBar

' Create the Toolbar
On Error Resume Next
CommandBars("NLV Printing ToolBar").Delete
On Error GoTo 0
Set TBar = CommandBars.Add
With TBar
.Name = "NLV Printing ToolBar"
.Top = 250
.Left = 700
.Visible = True
End With

'print a unit
Set NewBtn = CommandBars("NLV Printing ToolBar").Controls.Add(Type:=msoControlButton)
With NewBtn
.BeginGroup = True
.Style = msoButtonIconAndCaption
.FaceId = 4
.OnAction = "HideandPrint"
.Caption = "Print Unit"
.TooltipText = "Be sure you are on a Unit page."
End With

'print a Division
Set NewBtn = CommandBars("NLV Printing ToolBar").Controls.Add(Type:=msoControlButton)
With NewBtn
.BeginGroup = True
.Style = msoButtonIconAndCaption
.FaceId = 4
.OnAction = "Hideprintdiv"
.Caption = "Print Division"
.TooltipText = "Be sure you are on a Division Page"
End With

'print a Department
Set NewBtn = CommandBars("NLV Printing ToolBar").Controls.Add(Type:=msoControlButton)
With NewBtn
.BeginGroup = True
.Style = msoButtonIconAndCaption
.FaceId = 4
.OnAction = "HidePrintdep"
.Caption = "Print Department"
.TooltipText = "Be sure you are on a Department page"
End With

End Sub

[skatonni]

Try this code in the This Workbook module.

Please Login or Register  to view this content.

[Beeblebrox]

thanks
That worked.