Thanks Bob,
Works much better, I'm afraid I a bit of a novice so can
you answer me a few questions, please?
How do you changed the toolbar name from Custom to
something more useful?
Is there a way of displaying the buttons downward, rather
than across, and finally!
How is the toolbar deleted when the spreadsheet is closed?
Mark
-----Original Message-----
Mark,
I would not attach a toolbar to a template, the fact that
you create many
copies from that template can only complicate matters. I
would create the
toolbars from a one instance workbook, such as the
Personal.xls workbook, or
from within an addin.
Furthermore, I always create my toolbars from code, not
manually. Here is a
simple example that creates a toolbar, adds various
buttons, with icons and
text, and positions the toolbar. This code would normally
go in a workbook
open event in Thisworkbook
On Error Resume Next
Application.CommandBars(appMenu).Delete
On Error GoTo 0
Set oCB = Application.CommandBars.Add(Name:=appMenu,
temporary:=True)
With oCB
With .Controls.Add(Type:=msoControlButton)
.Caption = appMenu & " Toolbar"
.Style = msoButtonCaption
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Open File"
.FaceId = 23
.Style = msoButtonIconAndCaption
.OnAction = "OpenFiles"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Sort Results"
.FaceId = 210
.Style = msoButtonIconAndCaption
.OnAction = "BCCCSort"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Player"
.FaceId = 316
.Style = msoButtonIconAndCaption
.OnAction = "NewEntry"
End With
With .Controls.Add(Type:=msoControlDropdown)
.BeginGroup = True
.Caption = "Delete"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Delete "
.Style = msoButtonCaption
.OnAction = "RemoveEntry "
.Parameter = "Toolbar"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Sheet"
.FaceId = 18
.Style = msoButtonIconAndCaption
.OnAction = "NewSheet"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Workbook"
.FaceId = 245
.Style = msoButtonIconAndCaption
.OnAction = "NewBook"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "About..."
.FaceId = 941
.Style = msoButtonIconAndCaption
.OnAction = "About"
End With
.Visible = True
.Position = msoBarTop
End With
To get a utility to see what FaceIds are available, visit
JOhn Walkenbach's
site at http://j-walk.com/ss/excel/tips/tip67.htm
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Mark" wrote in
message
...
I am using Excel 97.
I have written some temples in Excel which utilise some
code in VBA to complete some of the fields, these all
work
fine.
I also have a custom toolbar with buttons which call
macros in the same template.
For some reason the code behind the buttons don't stay
with the template. When I create a spreadsheet from one
of these templates, the path of the code changes and the
code can't be found. Any solutions would be
appreciated.
Mark
.