Positioning custom menu
 

I found some great code from Patrick Molloy for the above, controlled by
Workbook_Activate. Under normal circumstances it would place a new menu
before the Help menu, however I have (and others may also have) custom items
on the menu bar. I believe the section that controls the positioning is -

With CommandBars("Worksheet Menu Bar")
Set cmd = .Controls.Add(msoControlPopup, _
befo=.Controls.Count, _
temporary:=True)
End With
cmd.Visible = True

, presumably the "befo=.Controls.Count" bit, ie: one before however many
menu bar items there are.

Is there a way Excel can determine the actual number position of the Help
menu in the bar and place it there minus one?

TIA,
Andy

Positioning custom menu
 

Andy,

Here is some code that might help

Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
Set oMenu = Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:= _
msoControlPopup, Befo=HelpMenu.Index, Temporary:=True)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andy Brown" wrote in message
...
I found some great code from Patrick Molloy for the above, controlled by
Workbook_Activate. Under normal circumstances it would place a new menu
before the Help menu, however I have (and others may also have) custom
items
on the menu bar. I believe the section that controls the positioning is -

With CommandBars("Worksheet Menu Bar")
Set cmd = .Controls.Add(msoControlPopup, _
befo=.Controls.Count, _
temporary:=True)
End With
cmd.Visible = True

, presumably the "befo=.Controls.Count" bit, ie: one before however
many
menu bar items there are.

Is there a way Excel can determine the actual number position of the Help
menu in the bar and place it there minus one?

TIA,
Andy

Positioning custom menu
 

Here is some code that might help

Thanks Bob. That was definitely on the right track, unfortunately I couldn't
get it going in tandem with what I had from Patrick.

Rgds,
Andy

Positioning custom menu
 

Andy,

Post a follow-up with the extra details.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andy Brown" wrote in message
...
Here is some code that might help

Thanks Bob. That was definitely on the right track, unfortunately I
couldn't
get it going in tandem with what I had from Patrick.

Rgds,
Andy

Positioning custom menu
 

Post a follow-up with the extra details.

Bob, that may well just confuse things. The code you supplied works fine,
inasmuchas adds a new menu item before Help. All I need now is the syntax to
refer to it for the purpose of naming it, adding items to it, etc.

Rgds,
Andy

Positioning custom menu
 

Andy,

In my example, you refer to it through the oMenu object variable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andy Brown" wrote in message
...
Post a follow-up with the extra details.

Bob, that may well just confuse things. The code you supplied works fine,
inasmuchas adds a new menu item before Help. All I need now is the syntax
to
refer to it for the purpose of naming it, adding items to it, etc.

Rgds,
Andy

Positioning custom menu
 

the oMenu object variable.

OK, I swear yesterday I had "With oMenu, blah blah" and all it did was fall
over. But today it's captioned & running.

TVM for sticking with, you're a star.

Rgds,
Andy