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

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"




"JEB" wrote in message
...
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

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)



"JEB" wrote in message
...
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

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" wrote in message
...
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)



"JEB" wrote in message
...
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

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)



"Craig" wrote in message
...
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" wrote in message
...
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)



"JEB" wrote in message
...
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