CODEWITHSUNDEEP
excelvbacombobox
Junior Reis
Junior Reis

Reputation: 11

Changing Combobox variable on "For Loop"

im trying to optimize my code by adding item on multiples comboboxes depending on a previoues variable.

Quick explanation: I will ask the user for a number (1-5) and after i receive this variable i want to set how many itens will be add on more than one comboboxes, i've tried two different ways, none has worked.

Dim CB as Combobox

Variable_From_User = TextBox1.value


For I = Variable_From_User to 5

     *CB = "ComboBox" & I*

     CB.AddItem (Value_From_Sheet)
Next

It keeps returning the error:

Object variable or With block variable not set

Second Way:

Variable = 2

For n = 0 To 5
    With ComboBox & Variable
        .AddItem (n)
    End With
Next

Upvotes: 0

Views: 37

Answers (1)

FunThomas
FunThomas

Reputation: 29181

(Note: I assume that you are talking about a userform)

A combobox is a control on a Userform. You can access a control within the code of the Userform by its name, eg Me.Combobox1 (Me represents the form itself).

What doesn't work is to write ComboBox & Variable. This is a string concatenation and results in a string. You can't expect that VBA will magically figure out that this string is meant to be a variable (a member of the form class, to be precise) and use that.

If you know the the name of a control, you can access it via the Controls-Collection of the form. Note that you need to use Set as you are assigning an object.

Dim CB as Combobox
Variable_From_User = TextBox1.value
For I = Variable_From_User to 5
    Set CB = Me.Controls("ComboBox" & I)
    CB.AddItem Value_From_Sheet  ' <-- Don't use parentheses here
Next

Upvotes: 2

Related Questions