FaceID of a custom menu 2

[NuJoizey]

I created a custom menu that appears on top of excel like so:

File Edit View Insert.....Help | My Menu

My Menu has one choice under it called do this thing.

I want to change the FaceID of the do this thing choice.

It is simple, for example to access a menu choice of one of the excel native menus like so:

MsgBox Application.CommandBars("Help").Controls(1).FaceId

Yet, this does not work for accessing My Menu

How do I access the do this thing button on My Menu to make my desired change FaceID change?

I want to say My Menu.do this thing button.FaceID="343", but obviously, that doesn't work.....

 

[ck1999]

This may be a long way around but it works if I understand what you are trying to do

dim counter
dim newctl
For counter = Application.CommandBars.ActiveMenuBar.Controls.Count To 1 Step -1
    
    If Application.CommandBars.ActiveMenuBar.Controls(counter).Caption = "My Menu" Then
        Set newctl = Application.CommandBars.ActiveMenuBar.Controls(12)
        newctl.Controls(1).FaceId = "343"
        Exit For
    End If
Next counter

ck1999

 

[GVF]

It is usually easier if you set menus and commands as objects. Later, you can refer to the object you created instead of having to loop through a bunch of menu commands.

Public myMenu As CommandBarPopup
Public myCmd As CommandBarButton

Sub JunkMenu()
Set myMenu = CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup)
    myMenu.Caption = "New Menu"
Set myCmd = myMenu.Controls.Add(msoControlButton)
    myCmd.Caption = "My Command"
    myCmd.FaceId = 343
    myCmd.OnAction = "DoSomething"
End Sub

 

[NuJoizey]

awesome. thank you. I think your code example answers my question.

My problem in programming excel is that I find navigating the object model confusing - i.e. I never know what excel calls things.....is is a menu or a control, or a CommandBar. or what????? I'd probably be better at this if I programmed excel every single day!!

For example, it took me a while to figure out that the "Worksheet Menu Bar" is actually the proper name for the File Edit View Insert... etc that appears accross the top of excel documents, and that this is something that can be manipulated programatically, as you have shown.

Also, it took me a while to figure out what i was trying to add is a CommnadBarPopUp Control.

 

[NuJoizey]

GVF

ok here's one for you....

say you implement your code, and the macro "DoSomething" is
defined as simply:

MsgBox "hello"

When you click the "My Command" menu control, a messagebox saying "hello" pops up TWICE.

why oh why?

 

[NuJoizey]

oy vay. i figured it out.

it's because of this:

I was calling DoSomething incorrectly.

I wrote:
myCmd.OnAction = "DoSomething()"

when instead it should have been

myCmd.OnAction = "DoSomething"

There's no "()".

That'll mess you up - if you're not paying attention...