View Full Version : Using custom controls to call sub with argument

12-07-2008, 11:48 PM
I have to create a custom menu, and some buttons on that custom menu.
when those buttons are pressed I have to call a sub with arguments. I could call the sub without arguments but having problems in calling the sub with arguments.
here is the code

Public Sub BuildCustomMenu()

Dim i, j, k as Integer
Dim ctrl1 As CommandBarControl
Dim btn1 As CommandBarControl

Set ctrl1 = Application.CommandBars("Chart").Controls.Add _
(Type:=msoControlPopup, Before:=1, Temporary:=True)

i = 2
j = 3
k = 4
Set btn1 = ctrl1.Controls.Add
btn1.Caption = "cap1"
btn1.tag = "tag1"
btn1.OnAction = "mergeBuckets( i, j, k)"
' if I define Sub mergeBuckets without arguments and write btn1.OnAction = "mergebuckets" it runs fine...
End Sub

Public Sub mergeBuckets(CDB, firstRowInCDB, version As Integer)
Debug.Print "cp1 " & Now()
End Sub

Bob Phillips
12-08-2008, 01:41 AM
If you want to pass parameters to a menu option, better to find another way, such as setting the parameter and/or tag properties of the button, or if you need it dynamic, set gloabl variables.

12-08-2008, 01:44 AM
thanks... may be global variables will work in this case... but by your post I am getting a feeling like "vb sux"

one more thing... can we have multiple tags (or something like that) for one button, i mean something like this...
btn1.tag1 = i
btn1.tag2 = j
btn1.tag3 = k

by that way i wont need global variables i guess

Bob Phillips
12-08-2008, 01:48 AM
Why? Just because it isn't structured how you want it?

12-08-2008, 01:53 AM
sux as we can pass a sub, but can't pass a sub with arguments.
here I am calling this sub from same module, but what if want this sub to be called from other module, with my global variables as private.

I am newbie and sry if it sounds stupid, but i hope u understand my point.

Bob Phillips
12-08-2008, 02:51 AM
No I am afraid I don't. It is like speaking a language, you have to learn its syntax and use it the way it works. If you couldn't do what you want to do, maybe you would have a point, but you can, albeit not the way you want. In 11 years of commandbars, people have learnt to use it without any difficulty, you are not the first to ant to pass parameters.

12-08-2008, 02:54 AM
can you help me on this one thing.
how to pass an array to tag.
At msdn i read we can pass any object to tag. but i m facing difficulties in passing array to tag.

Bob Phillips
12-08-2008, 03:04 AM
I don't see how you can, but you can pass a delimited string and convert it to an array when reading the tag property.

12-08-2008, 03:06 AM
thanks... that will do.