Solved: Using OnAction correctly

[makako]

Hi, I created a toolbar with some controls that work pretty much the same way ( all of them delete rows with specific variables ), in my code, i can only get the ".OnAction = 'MySub' " to be a sub with no arguments. The code is very long and I would like to have it all in one sub, is there any way i can use the same sub? So far I have tried

".OnAction = 'MySub(1)'"
".OnAction = 'MySub 1'"
".OnAction = 'MySub' & '(1)' "
".OnAction = 'MySub' & ' 1' "
etc but i keep getting error saying vba cannot find the sub (ex: ...MySub(1)).

I have an idea by using application.caller etc but im not sure how to use it, thanks.

[Bob Phillips]

It is possible to create an argumnet via the OnACtion, but I strongly suggest that you avoid the temptation, it is bad-coding IMO, a maintenance headache.

Far better to set the Tag or Parameter property of the control, and pick that up in the OnAction procedure



With Application.CommandBars.ActionControl
Select Case .Parameter
Case 1: MsgBox 1
Case 2: MsgBox 2
End Select
End With

[makako]

Private Sub Workbook_Open()
Dim Menu As CommandBarPopup
Dim BMenu As CommandBarButton
Dim BMenuPop As CommandBarControl
Dim Count As Integer

With Menu
'General Menu
Set BMenuPop = Menu.Controls.Add(msoControlPopup)
With BMenuPop
'Generates Control 1
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 1"
.OnAction = "subControl(1)"
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
'Generates Control 2
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 2"
.OnAction = "subControl(2)"
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
End With
End With
End Sub

sorry, i dont get it, do i have to set instead of the onaction property an action control parameter? I have never used it. haw (and where ) do i create it in this toolbar?
pd: I have no clue if this has something to do but the toolbar is created and deleted everytime i run a specific workbook so i dont get the toolbar in all excel. thanks

[Bob Phillips]

As I said previously, set the Tag or Parameter property of the CONTROL.



Private Sub Workbook_Open()
Dim Menu As CommandBarPopup
Dim BMenu As CommandBarButton
Dim BMenuPop As CommandBarControl
Dim Count As Integer

With Menu
'General Menu
Set BMenuPop = Menu.Controls.Add(msoControlPopup)
With BMenuPop
'Generates Control 1
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 1"
.OnAction = "subControl"
.Tag = 1
.Parameter = 1 'take your pick
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
'Generates Control 2
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 2"
.OnAction = "subControl"
.Tag = 2
.Parameter = 2 'take your pick
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
End With
End With
End Sub

[Zack Barresse]

I think you'll have a hard time passing variables via the OnAction parameter.

[Bob Phillips]

I think you'll have a hard time passing variables via the OnAction parameter.

It can be done, but it is convoluted and a maintenance nightmare. Not worth the effort.

[Zack Barresse]

Not worth the effort.
Exactly. :)

[makako]

thanks, that worked perfectly. few questions about it,
in Application.CommandBars.ActionControl Im creating a reference to the commanbar that called the procedure? or in toolbars in general?, is the parameter unique in a toolbar? or is it shared by the entire application? (sorry, Ive never used it and havent found info about it)

[mdmackillop]

I'm getting a runtime error 91 on this line. Any thoughts?

Set BMenuPop = Menu.Controls.Add(msoControlPopup)

[Bob Phillips]

I'm getting a runtime error 91 on this line. Any thoughts?

Set BMenuPop = Menu.Controls.Add(msoControlPopup)


It is because he hasn't defined Menu at that point, it is still Nothing.

[Bob Phillips]

thanks, that worked perfectly. few questions about it,
in Application.CommandBars.ActionControl Im creating a reference to the commanbar that called the procedure? or in toolbars in general?, is the parameter unique in a toolbar? or is it shared by the entire application? (sorry, Ive never used it and havent found info about it)

The calling commandbar control.

[makako]

thanks

[mdmackillop]

It is because he hasn't defined Menu at that point, it is still Nothing.
Thanks Bob.