Custom Toolbar - Buttons, Macro Assignment.

[Guest]

I'm stumped.
I have created a custom toolbar in excel and would like to add additional
buttons to it. I would also like to change the macro name assignment to one
of the current buttons. How do I do it.

Thanking you in advance.

JEB

 

[Craig]

Put your cursor over the toolbar and right click

Go down to Customize

To add a new button:
Click on Commands
In the categories choose Macros and drag the "Custom Button" to your toolbar

Then right click on the button on your toolbar and choose "Assign Macro"

 

[Bob Phillips]

Always best to do it dynamically

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

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

Set oCB = Application.CommandBars.Add(Name:="myToolbar",
temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Craig]

Bob -

Quick question. I have always added macro buttons and assigned macros based
on my previous response. Just wondering what advantage it is to do it the
way you stated. Keep in mind, I am a novice and have no formal programming
education. Just wanting to make sure that if I continue to do it my way
that I won't run into problems down the road.

Also, I do most of my macros in a toolbar that is available in all my
workbooks because I store the toolbar and macros in an .xla file. Do you
see any problems with this? The only real problem I have had, is when I
have upgraded computers in the past, I am unable to copy the toolbar to the
new computer. I am able to copy the macros but I have to setup the toolbar
again and assign all the macros. Not a big deal just a pin. Is there any
other way of completing the copy?

Thanks.

 

[Bob Phillips]

You have answered you own question.

Your way, the toolbar is just not portable. If you create code that builds
the toolbar when the workbook opens (which can be your xla), it is part of
the workbook (the code is), and so it goes where the workbook goes.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)