Positioning custom menu

A

Andy Brown

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, _
before:=.Controls.Count, _
temporary:=True)
End With
cmd.Visible = True

, presumably the "before:=.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
 
B

Bob Phillips

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, Before:=HelpMenu.Index, Temporary:=True)

--

HTH

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

Andy Brown

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
 
B

Bob Phillips

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)
 
A

Andy Brown

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
 
B

Bob Phillips

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)
 
A

Andy Brown

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
 
Top