Macros on Excel Custom Toolbars.

[Mark]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Dave Peterson]

I bet Bob initialized that appMenu in his real code:

At the top of the module and procedure:

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.)