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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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,029
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.
 

Forum statistics

Threads
1,140,915
Messages
5,703,152
Members
421,278
Latest member
16cwilliams

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top