How do I pass parameters to the OnAction method

[sbendbuckeye]

Hello All,

I want to write some generalized code to handle custom menu item clicks.

A. Do I need to set up the OnAction property to tell it to expect parameters

B. How do I setup parameters in my code

Thanks in advance for any ideas and/or suggestions!

 

[Legacy 98055]

There are several ways to strore data within a particular control. My favorite is to use a comma separated list within the tag property. See the code example below.

UsingParametersInCommandBarControls.zip

In a standard module.
<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">With</font> Application.CommandBars(1).Controls
           <font color="#0000A0">With</font> .Add(msoControlButton, , , , True)
              <font color="#008000"> 'passing four arguments within the tag property</font>
               .Tag = "543.221,Hello,Goodbye,09/10/2006"
               .Parameter = "You can use this to pass an argument as well."
               .Style = msoButtonCaption
               .OnAction = "MyOnAction"
               .Caption = "Added Button One"
           <font color="#0000A0">End</font> <font color="#0000A0">With</font>
           <font color="#0000A0">With</font> .Add(msoControlButton, , , , True)
               .Tag = "111.435,Options,Selection,05/10/1970"
               .Parameter = "09/10/2006"
               .Style = msoButtonCaption
               .OnAction = "MyOnAction"
               .Caption = "Added Button Two"
           <font color="#0000A0">End</font> <font color="#0000A0">With</font>
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> MyOnAction()
       <font color="#0000A0">Dim</font> ClickedControl <font color="#0000A0">As</font> CommandBarButton, s() <font color="#0000A0">As</font> <font color="#0000A0">String</font>

       <font color="#0000A0">Set</font> ClickedControl = Application.CommandBars.ActionControl

       s() = Split(ClickedControl.Tag, ",")

      <font color="#008000"> 'you can then cast to the correct type</font>
       Debug.Print "You just clicked " & ClickedControl.Caption
       Debug.Print CDbl(s(0)) + 1
       Debug.Print s(1)
       Debug.Print s(2)
       Debug.Print DateAdd("d", 1, CDate(s(3)))
       Debug.Print ClickedControl.Parameter
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9162006151821447").value=document.all("9162006151821447").value.replace(/<br \/>\s\s/g,"");document.all("9162006151821447").value=document.all("9162006151821447").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9162006151821447").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9162006151821447" wrap="virtual">
Sub Example()
With Application.CommandBars(1).Controls
With .Add(msoControlButton, , , , True)
'passing four arguments within the tag property
.Tag = "543.221,Hello,Goodbye,09/10/2006"
.Parameter = "You can use this to pass an argument as well."
.Style = msoButtonCaption
.OnAction = "MyOnAction"
.Caption = "Added Button One"
End With
With .Add(msoControlButton, , , , True)
.Tag = "111.435,Options,Selection,05/10/1970"
.Parameter = "09/10/2006"
.Style = msoButtonCaption
.OnAction = "MyOnAction"
.Caption = "Added Button Two"
End With
End With
End Sub

Sub MyOnAction()
Dim ClickedControl As CommandBarButton, s() As String

Set ClickedControl = Application.CommandBars.ActionControl

s() = Split(ClickedControl.Tag, ",")

'you can then cast to the correct type
Debug.Print "You just clicked " & ClickedControl.Caption
Debug.Print CDbl(s(0)) + 1
Debug.Print s(1)
Debug.Print s(2)
Debug.Print DateAdd("d", 1, CDate(s(3)))
Debug.Print ClickedControl.Parameter
End Sub</textarea>

UsingParametersInCommandBarControls.zip

Other possible properties you might utilize could be the control's caption, id, index(position within parent), or even the position in some cases.

 

[tusharm]

See
Using procedures with arguments in non-obvious instances:
macros associated with shapes, forms, and commandbar elements, and called by the OnTime and OnKey methods
http://www.tushar-mehta.com/excel/vba/xl objects and procedures with arguments.htm

Hello All,

I want to write some generalized code to handle custom menu item clicks.

A. Do I need to set up the OnAction property to tell it to expect parameters

B. How do I setup parameters in my code

Thanks in advance for any ideas and/or suggestions!

 

[sbendbuckeye]

Hello,

Thanks to both of you for your very helpful responses!

I've been writing .Net code for a while and had forgotten some of the VBA syntax needed for a small Excel project I am currently doing. I knew I could count on MrExcel.com for some quick and timely examples to refresh my memory.

Tusharm, thanks for the link, I found it over the weekend when googling this problem a bit more.