Refer to Combobox passed as parameter by string variable name

[TC1980]

Hi All,

I feel like this should be easy but for the life of me I can't work it out!

I have a UserForm with various controls on it including some Comboboxes. When a change is made it triggers the change event and calls a sub in another module, passing the relevant controls as parameters:
N.B. I've simplified it here to just 3 of each control, when there are actually several.

Please Login or Register  to view this content.

The UpdateTotal2 Sub then checks if there are different currencies listed in the adjacent comboboxes and if they are all the same totals the textbox values and populates a total:

Please Login or Register  to view this content.

The problem occurs with this line of code:

Please Login or Register  to view this content.

If I simply use:

Please Login or Register  to view this content.

Then the code works fine, but that will require me to reiterate the same line of code for every combobox of which there are many (simplified here). I've tried every variation I can think of, msforms.combox, UserForm2.Controls, .name, Val("cbo" & objNo), etc, etc.

Is there a way that I can reference the parameter by string variable?

Thanks, TC

[xlnitwit]

Hi,

If that code is also in the userform, you may use

Please Login or Register  to view this content.

[TC1980]

Hi xlnitwit,

No, the UpdateTotal2 sub is held in a separate module. If I substitute the "Me." with the userform name I get the error: "Could not find the specified object". Presumably because it is looking for a control named cbo2 rather than Combobox10.

I suppose I could move the code to the userform to avoid having to pass the parameter, it's just really bugging me as I feel like there should be a way to do this...

[Kyle123]

Why would you want to? It's much more logical for the code to be in the userform.

Would could pass the userform as a parameter of the sub so you could have something like:

Please Login or Register  to view this content.

[TC1980]

Hi Kyle123,

Because the same piece of code is referenced by more than one userform, so it made sense to me to store it centrally so both could access rather than duplicating in each userform.

Wouldn't passing the Userform be exacly the same as I tried above, resulting in "Could not find the specified object"? As there is no such control named cbo in the userform, rather it refers to a parameter that contains the combobox name...?
But I can see how passing just the userform and calling the controls by their 'real' name would work. so thanks, that's given me something to work with.

[Kyle123]

Yes, sorry, I misunderstood what you are trying to do.

Do you have multiple identical userforms?

Are you calling that same function whenever any of those controls change?

[xlnitwit]

I fear I misunderstood your intent. You might perhaps pass one array of controls instead of three separate parameters.

Please Login or Register  to view this content.

unless I still have not understood.

[TC1980]

No worries Kyle123.

They aren't identical userforms, different but require the same function, i.e. totalling textboxes if the currency is the same. And yes, it's called by the textboxes change event where the cash value is input and also the comboboxes change event when the currency is changed. May seem a bit overkill but the only way I could find to get 'live' updating.

[TC1980]

xlnitwit, ah okay.

So by doing this, presumably it will 'pass' all comboboxes over, rather than the three given in the example? So cbo(1) would mean Combobox1? Rather than Combobox5 as when passed individually?

Not a problem as I can use a multiplier to get the correct Combobox name, just want to make sure I'm understanding it right?

[Arkadi]

You could use a class module and then add the textboxes to the class... then have the class handle their behaviour.

[xlnitwit]

cbo(1) would be the first item in the array that you pass to the routine. If you wish to refer to them by name instead, you might use a Dictionary or Collection rather than an array. For any of these options, you will need to alter the way you are passing the controls currently. If you don't want to do that, you might use a ParamArray instead.

[TC1980]

So forgive my ignorance here xlnitwit, how would I pass select comboboxes as an array?

Please Login or Register  to view this content.

I've only worked with basic string arrays really.

Arkadi, I'd read about creating a class module and I'm guessing there are other more suitable alternatives but I don't really want to cause myself a huge amount of rework if it can be avoided.

[xlnitwit]

If you wish to use the Array function, you will need to alter the routine declaration thus

Please Login or Register  to view this content.

Otherwise you will need to explicitly declare an array of combobox type and populate it with a loop.

[TC1980]

Okay so

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I will give it a try. Thanks for your help.

[TC1980]

Works perfectly, thanks all.

For clarity this is the code I used:

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

TC