Call sub with arguments from .onaction (command bar button)

[Andibevan]

I have created a command bar and added 1 button as follows:-

Set myBar = CommandBars.Add(, position:=msoBarPopup, Temporary:=True)
Set CTRL_Test = myBar.Controls.Add(Type:=msoControlButton)

I want the button to call a sub with an argument, the sub is as follows

Public Sub testercmd(nn As Variant)
MsgBox nn
End Sub

I am trying to set the onaction as follows, but every combination of quotes
and brackets doesn't work? Can you call a sub with arguments in this way or
can you only call functions with arguments from onaction?

I am currently trying as follows:-

With CTRL_Test
.Caption = "Test Button"
.OnAction = "'testercmd ""12""'"
End With

TIA

Andi

 

[Marshall Barton]

I have created a command bar and added 1 button as follows:-

Set myBar = CommandBars.Add(, position:=msoBarPopup, Temporary:=True)
Set CTRL_Test = myBar.Controls.Add(Type:=msoControlButton)

I want the button to call a sub with an argument, the sub is as follows

Public Sub testercmd(nn As Variant)
MsgBox nn
End Sub

I am trying to set the onaction as follows, but every combination of quotes
and brackets doesn't work? Can you call a sub with arguments in this way or
can you only call functions with arguments from onaction?

I am currently trying as follows:-

With CTRL_Test
.Caption = "Test Button"
.OnAction = "'testercmd ""12""'"
End With

Help is really deficient on this topic.

I believe that the action procedure must be a Function, not
a Sub, and that the OnAction must start with an = sign.
Without the = sign, the property refers to a macro instead
of a procedure.

Public Function testercmd(nn As Variant)
MsgBox nn
End Function

and

With CTRL_Test
.Caption = "Test Button"
.OnAction = "=testercmd(12)"
End With

 

[stefan hoffmann]

hi,

I have created a command bar and added 1 button as follows:
Set myBar = CommandBars.Add(, position:=msoBarPopup, Temporary:=True)
Set CTRL_Test = myBar.Controls.Add(Type:=msoControlButton)

I am using the Parameter property:

With CTRL_Test
.Caption = "Test Button"
.OnAction = "testercmd" .Parameter = "Test"
End With

Public Sub TesterCmd()

Select Case CommandBars.ActionControl.Parameter
Case Is = "Test"
Case Else
MsgBox "Unkown operation requested."
End Select

End Sub



mfG
--> stefan <--

 

[Andibevan]

Thanks to you both - I have now come to the conclusion that you can't call a
sub with an argument directly from the button.

I hadn't thought about stefan's suggestion, thanks

 

[Marshall Barton]

What does it matter to you if it's a Sub or a Function?? My
most common action looks like:
=MyFunction(Screen.ActiveControl)

I think that is especially useful for use with forms because
the function name is first resolved in the active form,
which allows each form to provide its own context sensitive
function and, if the function is not found in the active
form, then it looks for the function name in a standard
module.

 

[Andibevan]

Thanks Marsh - there is no reason for using a sub, I was just being subborn
this afternoon trying to get it to work with a sub as I couldn't find
anything in the help saying it wasn't possible. Like you pointed out - The
help is quite deficiant in this topic.

Ta

Andi

What does it matter to you if it's a Sub or a Function?? My
most common action looks like:
=MyFunction(Screen.ActiveControl)

I think that is especially useful for use with forms because
the function name is first resolved in the active form,
which allows each form to provide its own context sensitive
function and, if the function is not found in the active
form, then it looks for the function name in a standard
module.
--
Marsh
MVP [MS Access]

Thanks to you both - I have now come to the conclusion that you can't call a
sub with an argument directly from the button.

I hadn't thought about stefan's suggestion, thanks