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!