Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Help - cell reference
Have a large workbook and placing a menu to manuever around. I want to base
the menu list off a cell range. Names may change. I cant figure this out Dim myMnu As Object Set myMnu = CommandBars("Worksheet menu bar").Controls. _ Add(Type:=msoControlPopup, befo=2) With myMnu .caption = "&Volunteers" .Controls.Add(Type:=msoControlButton, befo=1).caption = Sheet1.Range("m21") ..Controls(Sheet1.Range("m21")).OnAction = "Vol1" (blows out here) End with The values match. I have created menus for successfully with the same code with the exception of using the range ie caption ="text" .... ..controls("text).OnAction etc Where am I going wrong. MUCH thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu Help - cell reference
This worked for me:
.Controls(Sheet1.Range("m21").Text).OnAction = "Vol1" Unless you need the object variables, though, my preference would be to use something like this: With CommandBars("Worksheet menu bar").Controls.Add( _ Type:=msoControlPopup, befo=2) .Caption = "&Volunteers" With .Controls.Add(Type:=msoControlButton, befo=1) .Caption = Sheet1.Range("m21").Text .OnAction = "Vol1" End With End With In article , Dan wrote: Have a large workbook and placing a menu to manuever around. I want to base the menu list off a cell range. Names may change. I cant figure this out Dim myMnu As Object Set myMnu = CommandBars("Worksheet menu bar").Controls. _ Add(Type:=msoControlPopup, befo=2) With myMnu .caption = "&Volunteers" .Controls.Add(Type:=msoControlButton, befo=1).caption = Sheet1.Range("m21") .Controls(Sheet1.Range("m21")).OnAction = "Vol1" (blows out here) End with The values match. I have created menus for successfully with the same code with the exception of using the range ie caption ="text" .... .controls("text).OnAction etc Where am I going wrong. MUCH thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop dwn menu. Formula to count selection frm menu in anoth cell? | Excel Worksheet Functions | |||
Reference Dropdown Menu Items in Macro | Excel Programming | |||
How Do I Reference Individual Dropdown Menu Items in VBA? | Excel Programming | |||
How to reference # of copies in print menu? | Excel Programming | |||
relative reference button on end macro menu | Excel Programming |