Macros on Excel Custom Toolbars.
 

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

Macros on Excel Custom Toolbars.
 

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

Macros on Excel Custom Toolbars.
 

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


.

Macros on Excel Custom Toolbars.
 

I bet Bob initialized that appMenu in his real code:

At the top of the module and procedu

Option Explicit
Public appMenu As String
Sub auto_open()

Dim oCb As CommandBar
appMenu = "myToolbarNameHere"

On Error Resume Next
Application.CommandBars(appMenu).Delete
On Error GoTo 0

Set oCb = Application.CommandBars.Add(Name:=appMenu, temporary:=True)

.....

End Sub

Notice the name is now auto_open. It'll run when you open the workbook.

To clean things up, you could have an auto_close:

Sub auto_close()
On Error Resume Next
Application.CommandBars(appMenu).Delete
On Error GoTo 0
End Sub

And you could change the ".Position = msoBarTop" to "msoBarLeft" to move the
commandbar to the left margin.

You could even make it
..position = msoBarFloating
'and add the position
..Top = 0.25
..Left = 0.11
..Width = 100
..Height = 400

(move it where you want to.)





wrote:

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


.


--

Dave Peterson