Changing a ComboBox reference using a Variable

[Muggles]

Hey there,

I am trying to create a code where I can do do the following With command for a ComboBox where it uses a variable to change the ComboBox name.

Currently it is:


With Sheet1.ComboBox1
.AddItem Sheet1.Range("B" & i).Value
End With

But I would like it to be something like

Dim Variable

Variable = 1

With Sheet1."ComboBox" & Variable
.AddItem Sheet1.Range("B" & i).Value
End With

Is there a way to easily do this?

Thanks so much in advance for your help!

 

[Akuini]

@Muggles
Welcome to MrExcel
Example:

i = 1
MsgBox Sheets("Sheet1").OLEObjects("combobox" & i).Object.Value

 

[Muggles]

Thank you so much for your quick reply!

When i've gone to do this I get an error:

Run-time error '9"
Subscript out of range

Any ideas on what i'm doing wrong here?

 

[Akuini]

Are you using Active x control or Form control?

 

[Akuini]

If it is an Active x control then something like this:

Sub try()
Dim i As Long
Dim n
n = 1
i = 4
With Sheet1.OLEObjects("combobox" & n).Object
    .AddItem Sheet1.Range("B" & i).Value
End With
End Sub

 

[Muggles]

Thanks that got it, it was the fact it was Active X.

Appreciate your help!