PDA

View Full Version : Use Variable in ComboBox name?



speedracer
02-04-2007, 08:20 PM
Is there a way to use a variable in a ComboBox name or for that matter a Macro name?

In other words, I want to be able to use a variable in order to count through a series of combo boxes and perform various actions on each of them (i.e copying the values of 20 comboboxes into various cells on another sheet)

I am inclined to reference the ComboBox like this if the variable a=1 (or whatever) Cells(a,1)=ComboBox(a).value, but obviously that is not allowed. Is there a simple way to do this?

Is there a way to call a macro in a similar fashion as well?

SRM

Bob Phillips
02-05-2007, 02:33 AM
What sort of combobox, a userform, forms toolbar or control toolbox combo?

JimmyTheHand
02-05-2007, 04:48 AM
On a userform, you can refer to a combobox as
Me.Controls("Combobox name")
The combobox name is a string that you can put together from constants and variables, as you wish. E.g.
Dim N as Long
N = 1
Me.Controls("Combobox" & CStr(N)).Text = "ABC"

will put the string "ABC" into the control called Combobox1
You need to devise proper combobox names at design time, and then you can reference them from within a loop, using the loop variable, for example.
A simple case is presented in the attachment.

speedracer
02-05-2007, 10:47 AM
Thanks so much! That is as perfect a solution as I could have asked for.

I have seen the ME thing before. I just did a search online, and couldnt find anything that explained it. Perhaps you might elighten me (pardon the pun!)

SRM

Bob Phillips
02-05-2007, 10:58 AM
It refers to the containing object, the userform in this case. Saves having to be explicit.