Positioning custom menu

  • Thread starter Thread starter Andy Brown
  • Start date Start date
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
 
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)
 
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
 
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)
 
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
 
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)
 
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
 
Back
Top