Solved: Onaction property

[justdriving]

Hi,

I am looking for help w.r.t. Onaction property.
I got error while executing this program.
Please advice where I went wrong.



Private Sub TestM()

Dim cMenu1 As CommandBarControl

Dim cbMainMenuBar As CommandBar

Dim iHelpMenu As Integer

Dim cbcCutomMenu As CommandBarControl

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)

cbcCutomMenu.Caption = "&Menu"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

.Caption = "A&nt"

.OnAction = "ANT"

End With



With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

.Caption = "&OT"

.OnAction = "OT"

End With


With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

.Caption = "LA"

.OnAction = "LA"

End With

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Mo&re"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton) 'Added a contol to the sub menu

.Caption = "&Charts"

.FaceId = 420

.OnAction = "ChartSheet"

End With

End Sub

Private Sub ANT()
msgbox "ANT"
End Sub


Private Sub OT()
msgbox "OT"
End Sub

Private Sub LA()
msgbox "LA"
End Sub

Private Sub ChartSheet()
msgbox "ChartSheet"
End Sub

[GTO]

Hi there,

Move the subs being called to a Standard Module.

Hope that helps,

Mark

[Bob Phillips]

And make them public!

[GTO]

@XLD:

Most importantly, an overdue "Howdy!", how's everything?


And make them public!

I was going to include that, but by happenstance, I forgot to ditch the 'Private'(s) when moving the OP's subs to a standard module in testing. I near fell over when I caught that it was calling the Private Sub(s) fine!

In recalling that OnAction acts like Application.Run when needing to provide the macroname and arguments all as a string, I did more testing to see if it would call a private procedure like Application.Run.

Yep!

Barring some discovery at work tomorrow (we now have 2010), .OnAction can call a private procedure in a standard module, in an object module (ThisWorkbook), or even pass an object variable to a private procedure in an object module!

Mark

Current personal motto: Ribbons are still for wrapping gifts...

[Aussiebear]

Current personal motto: Ribbons are still for wrapping gifts...

Welcome to Excel.

[GTO]

Welcome to Excel.

Well, it is, as it should be, a family friendly environment here. Suffice it to say, 'Stellar,' 'Excel,' 'Great' and such are not the words escaping my mouth the past several weeks. I recall a post (albeit loosely) in which Bob gave some advice. I think the guy or gal was asking reference 2007 vs 2010 and I'm sure Bob said 2010. The point I absorbed though, was akin to "embrace it and don't look back.' Certainly sensible and of course I am simply now experiencing what most did several years ago. On the bright side, I got to skip 2007 at least. Still, you might as well expect to hear some whining (probably not that much). I find looking for stuff that I knew where it was before aggravating. That, and I do not need 15 bajillion "pictures" (icons). That's just slower...

I would in fairness give 'props' for some stuff. Whilst I don't really need a preview of what certain paste special options are going to look like (and find the screen flicker irritating), I think its a neat idea. Not sure if it'll keep those who need the help from picking the wrong choice, but minus the preview, I like the option in the context menu.

[justdriving]

Hi all,

Questions and answers helped me learn more. I have one more query related to above VBA program. This VBA program will create Menu on Excel as following: -


Menu
||
ANT
||
OT
||
LA
||
MORE = CHARTS

I want to change this structure


Menu
||
ANT
||
OT
||
--
LA = LosAngeles
LA = California
--
||
MORE = CHARTS

Here, "=" represents Sub-branches and
two "LA" represents a single "LA" having two sub-branches "LosAngeles" and "California".

Please advice how can I do it?

[Bob Phillips]

Private Sub TestM()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
cbcCutomMenu.Caption = "&Menu"

With cbcCutomMenu

With .Controls.Add(Type:=msoControlButton)

.Caption = "A&nt"
.OnAction = "ANT"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "&OT"
.OnAction = "OT"
End With

With .Controls.Add(Type:=msoControlPopup)

.Caption = "LA"

With .Controls.Add(Type:=msoControlButton)

.Caption = "Los Angeles"
.OnAction = "LA"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "California"
.OnAction = "Calif"
End With
End With
End With

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
cbcCutomMenu.Caption = "Mo&re"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton) 'Added a contol to the sub menu

.Caption = "&Charts"
.FaceId = 420
.OnAction = "ChartSheet"
End With
End Sub

[justdriving]

Thanks to all of you. It solved my problem.