Menu Help - cell reference

[Dan]

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, before:=2)

With myMnu
.caption = "&Volunteers"
.Controls.Add(Type:=msoControlButton, before:=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

[JE McGimpsey]

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, before:=2)
.Caption = "&Volunteers"
With .Controls.Add(Type:=msoControlButton, before:=1)
.Caption = Sheet1.Range("m21").Text
.OnAction = "Vol1"
End With
End With



In article <[email protected]>,
Dan <[email protected]> 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, before:=2)
>
> With myMnu
> .caption = "&Volunteers"
> .Controls.Add(Type:=msoControlButton, before:=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