Custom Toolbar Loading issue

[Bishop]

I have the following code:

***On opening***

Private Sub Workbook_Open()

'The following code checks to see if the CDToolBar is present
'in Excel. If it isn't it puts it there. If it is it does nothing
Dim cbr As CommandBar

On Error Resume Next
Set cbr = Application.CommandBars("TSToolBar")
On Error GoTo 0

If cbr Is Nothing Then
Call TallySheetToolBar
Call AddCustomControl
End If

If ThisWorkbook.Name Like "Master*" Then NotSoFast.Show
End Sub

***Toolbar Code***

Sub TallySheetToolBar()

Dim TSToolBar As CommandBar

Set TSToolBar = CommandBars.Add(temporary:=True)
With TSToolBar
.Name = "TSToolBar"
.Position = msoBarTop
.Visible = True
End With
End Sub

***Add Button Code***

Sub AddCustomControl()

Dim CBar As CommandBar
Dim CTTally As CommandBarControl 'Catalyst To Tally
Dim PFNum As CommandBarControl 'PF Number
Dim CRData As CommandBarControl 'Clear Rep Data

Set CBar = CommandBars("TSToolBar")
Set CTTally = CBar.Controls.Add(Type:=msoControlButton)
Set PFNum = CBar.Controls.Add(Type:=msoControlButton)
Set CRData = CBar.Controls.Add(Type:=msoControlButton)

With CTTally
.FaceId = 1763
.OnAction = "CatalystToTally"
End With

With PFNum
.FaceId = 643
.OnAction = "PFNumber"
End With

With CRData
.FaceId = 67
.OnAction = "ClearRepData"
End With

CBar.Visible = True

End Sub

Here is what's happening. Say I haven't loaded the toolbar yet. So I open
WB1 and the toolbar loads for the first time. I use the toolbar and it works
like it's supposed to. I close WB1 and open WB2. Once this toolbar loads it
stays in Excel. If I try to use the CTTally button it opens WB1 and executes
the code there. It never executes the CatalystToTally code for the current
WB (WB2) like it's supposed to. BUT, if I delete the custom toolbar, reload
it and reload the buttons it works fine. What would cause this behaviour?
If I change the WorkBook_Open procedure to delete the toolbar and reload it
everytime that (in theory) should do the trick but seems like a "hack" rather
than a fix. What causes the toolbar to "remember" the last WB?

 

[jaf]

Hi,
What does .OnAction = "CatalystToTally" do?

Yes, it calls the sub CatalystToTally.
Where is that sub located? In wb1?
Does the sub use wb1 by name, Thisworkbook or activeworkbook?

John

 

[Paul C]

I had a similar issue with a toolbar I used in several workbook. What I did
to fix any problems was delete the custom bar (if it exists, due to a crash
or something else) in my auto open and reload it for each workbook. On the
auto close I delete the tool bar when closing the workbook.

Sub Autpen()

' DELETE ANY OLD MENU THAT MAY HAVE BEEN LEFT FROM CRASH.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Cost Rollup
Tools").Delete
On Error GoTo 0


' INSTALL CUSTOM MENU
Set newitem = Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:= _
msoControlPopup, before:=11)
With newitem
.Caption = "&Cost Rollup Tools"
End With
'Any other commands

End Sub

Sub Auto_Close()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Cost Rollup
Tools").Delete
On Error GoTo 0
End Sub


As an alternative, you could create an .xla add on and have the tool bar
load when you open Excel. One down side to this method is that every user
who needs the tool bar would have to load the add in.