Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbar Loading issue
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbar Loading issue
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 "Bishop" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbar Loading issue
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, befo=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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Toolbar not working correctly in IE after loading Excel doc in bro | Excel Discussion (Misc queries) | |||
Loading custom toolbar | Excel Discussion (Misc queries) | |||
addin loading issue | Excel Discussion (Misc queries) | |||
custom toolbar buttons are saved where? Excel loads twice bymistake and all my custom toolbar buttons get gone!!! | Excel Programming | |||
loading a template from the network on toolbar | Excel Programming |