Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding events to newly add Menu
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding events to newly add Menu
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:=Tr ue) 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:=Tr ue) 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 "Amod" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding events to newly add Menu
Hello sir Thanks for your precious help. regards Amod -----Original Message----- 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:=T rue) 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:=T rue) 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 "Amod" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding dates to events | Excel Discussion (Misc queries) | |||
Adding buttons to a new menu bar | Excel Worksheet Functions | |||
Adding a menu | Excel Discussion (Misc queries) | |||
Add list to newly created menu | Excel Discussion (Misc queries) | |||
add list to newly created menu | Excel Worksheet Functions |