Adding events to newly add Menu

  • Thread starter Thread starter Amod
  • Start date Start date
A

Amod

hi

All
I am adding a new menus in excel. these menus are
generated dynamically. there is no fix number of menus. I
am generating it using loop.

now i want to assign a click event to every menuitem, in
termes of VBA for every office.CommandBarButton

I have declared one object of this type with withevent
keyword.
the problem is how do I assign these many menusitems to
this withevent object.

It is not possible to create that many withevents object
dynamically for each menuitem.

please send your answers
waiting for reply
regards
Amod
 
Amod,

This is a good time to use a class module. Create a class module called
CCmdControl and insert the following code:

Public WithEvents CmdButton As Office.CommandBarButton
Private Sub CmdButton_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
MsgBox "You clicked: " & Ctrl.Caption
End Sub

Then, in a standard code module, insert the following code:

Public Coll As New Collection

Sub CreateMenu()
Dim CtrlObj As CCmdControl
Dim Ctrl As Office.CommandBarButton
' create the command bar button here
Set Ctrl = Application.CommandBars("Worksheet Menu
Bar").Controls("Tools").Controls.Add(temporary:=True)
With Ctrl
.Caption = "Click Me"
.Visible = True
' set other properties
End With
Set CtrlObj = New CCmdControl
Set CtrlObj.CmdButton = Ctrl
Coll.Add CtrlObj

Set Ctrl = Application.CommandBars("Worksheet Menu
Bar").Controls("Tools").Controls.Add(temporary:=True)
With Ctrl
.Caption = "Click Me2"
.Visible = True
' set other properties
End With
Set CtrlObj = New CCmdControl
Set CtrlObj.CmdButton = Ctrl
Coll.Add CtrlObj
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Back
Top