How do I pass parameters to the OnAction method

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
L

Legacy 98055

Guest
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="filter:progid: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

MrExcel MVP
Joined
May 28, 2002
Messages
11,026
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

Active Member
Joined
Nov 26, 2003
Messages
440
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,919
Members
410,712
Latest member
jhgeorge
Top