Hi,
I write a macro in Excel. In order to run my macro conveniently, I want
to add a button in Excel tool bar. Would you tell me how to do? Thank you
very much!

Option Explicit


Sub AddMenu()

Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton

KillMenu

Set ctrlMain = CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)

With ctrlMain
.Caption = "M&y Menus"

Set ctrlItem = .Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "Proc &1"
.OnAction = "mnuProc1"
End With
Set ctrlItem = .Controls.Add(Type:=msoControlPopup)
With ctrlItem

.Caption = "Proc &2"
.BeginGroup = True

Set ctrlSubItem = .Controls.Add(Type:=msoControlButton)
With ctrlSubItem
.Caption = "Sub 2&a"
.OnAction = "mnuProc2subA"
End With
Set ctrlSubItem = .Controls.Add(Type:=msoControlButton)
With ctrlSubItem
.Caption = "sub 2&b"
.OnAction = "mnuProc2SubB"
End With
End With
Set ctrlItem = .Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "Proc &3"
.BeginGroup = True
.OnAction = "mnuProc3"
End With
End With

With CommandBars.Add("MyPopup", msoBarPopup, , True)
With .Controls.Add(msoControlButton)
.Caption = "A"
.FaceId = 3825
End With
With .Controls.Add(msoControlButton)
.Caption = "B"
.FaceId = 3826

End With

End With

End Sub
Sub KillMenu()
Dim cmdbar As CommandBar
On Error Resume Next
Set cmdbar = CommandBars("Worksheet Menu Bar")
cmdbar.Controls("My Menus").Delete
CommandBars("MyPopup").Delete
On Error GoTo 0

End Sub
Private Sub mnuProc1()
MsgBox "proc1"
End Sub
Private Sub mnuProc2subA()
MsgBox "proc2_A"
End Sub
Private Sub mnuProc2subB()
MsgBox "proc2_B"
End Sub
Private Sub mnuProc3()
MsgBox "proc3"
End Sub



"Terry" wrote:

Hi,
I write a macro in Excel. In order to run my macro conveniently, I want
to add a button in Excel tool bar. Would you tell me how to do? Thank you
very much!

This sub to create a tool bar and assign macro "Contact" to a button of
the tool bar every time the file is open.

Remember to delete this tool bar when you close the file. If not this
will end up an error at second time because the tool bar is already
exist in the tool bar list.

Good luck!
Rock

Public Sub workbook_open()

'Add the "Test" toolbar
Toolbars.Add "Test"
Toolbars("Test").ToolbarButtons.Add 163

With Toolbars("Test")
..Position = xlFloating
..Left = 650
..Top = 230
..Width = 250
End With
'Assigning an action to a button
With Toolbars("Test").ToolbarButtons(1)
..Name = "Contact us"
..OnAction = "Contact" 'Remember to have a macro name “Contact” to
assign
End With

End Sub


--
Rock
------------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
View this thread: http://www.excelforum.com/showthread...hreadid=494519

Thank you for your help!

"Patrick Molloy" ...
Option Explicit


Sub AddMenu()

Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton

KillMenu

Set ctrlMain = CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)

With ctrlMain
.Caption = "M&y Menus"

Set ctrlItem = .Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "Proc &1"
.OnAction = "mnuProc1"
End With
Set ctrlItem = .Controls.Add(Type:=msoControlPopup)
With ctrlItem

.Caption = "Proc &2"
.BeginGroup = True

Set ctrlSubItem = .Controls.Add(Type:=msoControlButton)
With ctrlSubItem
.Caption = "Sub 2&a"
.OnAction = "mnuProc2subA"
End With
Set ctrlSubItem = .Controls.Add(Type:=msoControlButton)
With ctrlSubItem
.Caption = "sub 2&b"
.OnAction = "mnuProc2SubB"
End With
End With
Set ctrlItem = .Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "Proc &3"
.BeginGroup = True
.OnAction = "mnuProc3"
End With
End With

With CommandBars.Add("MyPopup", msoBarPopup, , True)
With .Controls.Add(msoControlButton)
.Caption = "A"
.FaceId = 3825
End With
With .Controls.Add(msoControlButton)
.Caption = "B"
.FaceId = 3826

End With

End With

End Sub
Sub KillMenu()
Dim cmdbar As CommandBar
On Error Resume Next
Set cmdbar = CommandBars("Worksheet Menu Bar")
cmdbar.Controls("My Menus").Delete
CommandBars("MyPopup").Delete
On Error GoTo 0

End Sub
Private Sub mnuProc1()
MsgBox "proc1"
End Sub
Private Sub mnuProc2subA()
MsgBox "proc2_A"
End Sub
Private Sub mnuProc2subB()
MsgBox "proc2_B"
End Sub
Private Sub mnuProc3()
MsgBox "proc3"
End Sub



"Terry" wrote:

Hi,
I write a macro in Excel. In order to run my macro conveniently, I
want
to add a button in Excel tool bar. Would you tell me how to do? Thank you
very much!