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 Auto_Open()
' 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.
--
If this helps, please remember to click yes.
"Bishop" wrote:
> 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?
|
Similar Threads
