Toolbar Troubles

[brownti via OfficeKB.com]

I have some code that creates two toolbars when a template workbook is opened.
These tools bars do various things in the workbook, and then when i close
that workbook the toolbars are deleted using the sub beforeclose. however if
i have more than one workbook open that uses the code and i close one of them,
it deletes the toolbars and i cant use them in the still open workbook. i
tried just not deleting the toolsbars at close, but they stay linked to the
workbook that created them rather than acting on the open workbook. The code
that creates them is very long, so i wont post it all but here is the
begining where some of it is created:
Private Sub Workbook_Open()
LOADING.Show vbModeless
LOADING.Repaint
Dim DScb As CommandBar
Dim add_door As CommandBarButton
Dim oak As CommandBarButton
Dim raw As CommandBarButton
Dim maple As CommandBarButton
Dim pine As CommandBarButton
Dim alder As CommandBarButton
Dim cherry As CommandBarButton
Dim hrdwToolBar As String

hrdwToolBar = "Hardware"

Set CustomToolBar = Application.CommandBars.Add(Name:=hrdwToolBar,
Position:=msoBarTop)
CustomToolBar.Visible = True

Set HARDWARE = CustomToolBar.Controls.Add(Type:=msoControlPopup)
HARDWARE.Caption = "Hardware"

Set mouldings = CustomToolBar.Controls.Add(Type:=msoControlPopup)
mouldings.Caption = "Mouldings"

Set stairparts = CustomToolBar.Controls.Add(Type:=msoControlPopup)
stairparts.Caption = "Stair Parts"

With stairparts.Controls
Set newel = .Add(Type:=msoControlPopup)
newel.Caption = "Newels"
Set balusters = .Add(Type:=msoControlPopup)
balusters.Caption = "Balusters"
Set rails = .Add(Type:=msoControlPopup)
rails.Caption = "Rails"
End With

With rails.Controls
Set sch6005 = .Add(Type:=msoControlButton)
sch6005.Caption = "SCH-6005"
End With

With sch6005
.OnAction = "rail_run"
.Tag = "sch6005"
End With

Could the problem be with how the toolbars are created? Is there a better
way to create them once not on opening of workbook, but just create them and
then they will run the appropriate macros in the activeworkbook? Any
thoughts? Sorry if this is confusing.

 

[brownti via OfficeKB.com]

Any thoughts on this?

I have some code that creates two toolbars when a template workbook is opened.
These tools bars do various things in the workbook, and then when i close
that workbook the toolbars are deleted using the sub beforeclose. however if
i have more than one workbook open that uses the code and i close one of them,
it deletes the toolbars and i cant use them in the still open workbook. i
tried just not deleting the toolsbars at close, but they stay linked to the
workbook that created them rather than acting on the open workbook. The code
that creates them is very long, so i wont post it all but here is the
begining where some of it is created:
Private Sub Workbook_Open()
LOADING.Show vbModeless
LOADING.Repaint
Dim DScb As CommandBar
Dim add_door As CommandBarButton
Dim oak As CommandBarButton
Dim raw As CommandBarButton
Dim maple As CommandBarButton
Dim pine As CommandBarButton
Dim alder As CommandBarButton
Dim cherry As CommandBarButton
Dim hrdwToolBar As String

hrdwToolBar = "Hardware"

Set CustomToolBar = Application.CommandBars.Add(Name:=hrdwToolBar,
Position:=msoBarTop)
CustomToolBar.Visible = True

Set HARDWARE = CustomToolBar.Controls.Add(Type:=msoControlPopup)
HARDWARE.Caption = "Hardware"

Set mouldings = CustomToolBar.Controls.Add(Type:=msoControlPopup)
mouldings.Caption = "Mouldings"

Set stairparts = CustomToolBar.Controls.Add(Type:=msoControlPopup)
stairparts.Caption = "Stair Parts"

With stairparts.Controls
Set newel = .Add(Type:=msoControlPopup)
newel.Caption = "Newels"
Set balusters = .Add(Type:=msoControlPopup)
balusters.Caption = "Balusters"
Set rails = .Add(Type:=msoControlPopup)
rails.Caption = "Rails"
End With

With rails.Controls
Set sch6005 = .Add(Type:=msoControlButton)
sch6005.Caption = "SCH-6005"
End With

With sch6005
.OnAction = "rail_run"
.Tag = "sch6005"
End With

Could the problem be with how the toolbars are created? Is there a better
way to create them once not on opening of workbook, but just create them and
then they will run the appropriate macros in the activeworkbook? Any
thoughts? Sorry if this is confusing.

 

[Jim Rech]

There are two ways to go on this I think. One is to have each instance of
the template create its own toolbars when it's opened. Obviously the names
used would have to be dynamic. Coupled with this the templates should use
their activate and deactivate events to show/hide their toolbars so only one
set would be visible at a time. When a template is closed it would delete
its toolbars.

Another way would be to put all the toolbar code in a add-in. The add-in
code would be written so that when a toolbutton is clicked it runs on the
active workbook if it is a template file. When a template is opened it
would check to see if the add-in is already open and if not, open it. On
closing a template could determine if it's the last one and hide the toolbar
or close the add-in.

This is half baked, shot from the hip, so I'm sure other issues would pop up
with either of these approaches but it's a place to start.

--
Jim
| Any thoughts on this?
|
|
| brownti wrote:
| >I have some code that creates two toolbars when a template workbook is
opened.
| >These tools bars do various things in the workbook, and then when i close
| >that workbook the toolbars are deleted using the sub beforeclose.
however if
| >i have more than one workbook open that uses the code and i close one of
them,
| >it deletes the toolbars and i cant use them in the still open workbook.
i
| >tried just not deleting the toolsbars at close, but they stay linked to
the
| >workbook that created them rather than acting on the open workbook. The
code
| >that creates them is very long, so i wont post it all but here is the
| >begining where some of it is created:
| >Private Sub Workbook_Open()
| > LOADING.Show vbModeless
| > LOADING.Repaint
| >Dim DScb As CommandBar
| >Dim add_door As CommandBarButton
| >Dim oak As CommandBarButton
| >Dim raw As CommandBarButton
| >Dim maple As CommandBarButton
| >Dim pine As CommandBarButton
| >Dim alder As CommandBarButton
| >Dim cherry As CommandBarButton
| > Dim hrdwToolBar As String
| >
| > hrdwToolBar = "Hardware"
| >
| > Set CustomToolBar = Application.CommandBars.Add(Name:=hrdwToolBar,
| >Position:=msoBarTop)
| > CustomToolBar.Visible = True
| >
| > Set HARDWARE = CustomToolBar.Controls.Add(Type:=msoControlPopup)
| > HARDWARE.Caption = "Hardware"
| >
| > Set mouldings = CustomToolBar.Controls.Add(Type:=msoControlPopup)
| > mouldings.Caption = "Mouldings"
| >
| > Set stairparts = CustomToolBar.Controls.Add(Type:=msoControlPopup)
| > stairparts.Caption = "Stair Parts"
| >
| > With stairparts.Controls
| > Set newel = .Add(Type:=msoControlPopup)
| > newel.Caption = "Newels"
| > Set balusters = .Add(Type:=msoControlPopup)
| > balusters.Caption = "Balusters"
| > Set rails = .Add(Type:=msoControlPopup)
| > rails.Caption = "Rails"
| > End With
| >
| > With rails.Controls
| >Set sch6005 = .Add(Type:=msoControlButton)
| > sch6005.Caption = "SCH-6005"
| >End With
| >
| >With sch6005
| > .OnAction = "rail_run"
| > .Tag = "sch6005"
| >End With
| >
| >Could the problem be with how the toolbars are created? Is there a
better
| >way to create them once not on opening of workbook, but just create them
and
| >then they will run the appropriate macros in the activeworkbook? Any
| >thoughts? Sorry if this is confusing.
|
| --
| Message posted via OfficeKB.com
| http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200705/1
|