Search code examples
excelvbaloopscomboboxuserform

Excel VBA userform loop through combo boxes by changing name value

I have four combo boxes in an Excel userform with these value references:

Me.cbDesc1.Value
Me.cbDesc2.Value
Me.cbDesc3.Value
Me.cbDesc4.Value

What I want to do is loop through these combo box values so that the numbers 1-4 are changed with a variable, something like this...

For j = 1 to 4
    cell_j_in_sheet = "Me.cbDesc" & j & ".Value"
Next j

What I see in my cell is the string (e.g.) "Me.cbDesc1.Value" and not the actual value selected in the combo box. How do I instruct VBA to evaluate Me.cbDescj.Value and output the current contents of cbDescj through each iteration?

If there's better way to do this, please advise.

Solution

  • You can use the form's Controls property:

    For j = 1 to 4
    cell_j_in_sheet = Me.Controls("cbDesc" & j).Value
Next j