Re: Macros on Excel Custom Toolbars.

From: Dave Peterson (ec35720_at_msn.com)
Date: 03/06/04 

Date: Fri, 05 Mar 2004 21:41:01 -0600

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

anonymous@discussions.microsoft.com 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 spread*** 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 ***"
> > .FaceId = 18
> > .Style = msoButtonIconAndCaption
> > .OnAction = "New***"
> > 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" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:762201c4028d$02e91310$a501280a@phx.gbl...
> >> 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 spread*** 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
ec35720@msn.com