How to add a button in Excel tool bar in order to run my macro?
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!
|