Update numerous comboxes at once

[anon]

Hi,

I can update the rowsource of an individual combobox programatically
using the below;

ComboBox2.RowSource = "details!g4:g24"

however i need to do a lot at a time, looping through them one by one.
I had hoped I could use something similar to the below;

Dim cmbo
For i = 4 To 34
cmbo = ComboBox & i
cmbo.RowSource = "details!c4:c24"
next i

etc however this errors on the cmbo.RowSource part

I have tried changing the line to;

"ComboBox & i".RowSource

and this errors. I have also tried changing ComboBox & i to "ComboBox"
& i however this does not work either.

Any suggestions for where i'm going wrong?

My comboboxes are named ComboBox4 through to ComboBox34

Thanks,

 

[Dave Peterson]

Comboboxes on a userform?

dim i as long
for i = 4 to 34
me.controls("Combobox" & i).rowsource _
= worksheets("details").range("C4:c24").address(external:=true)
next i

 

[anon]

Yes my comboboxes are on a user form - sorry i forgot to state that.

I tried to post a simplified version of my code to make my explanation
easier, however here is the full version;

Sub updateranges()
Dim rng
Dim cmbo As ComboBox
j = 4
i = 70
rng = Sheets("sheet4").Range("d" & j).Value
For j = 4 To 34
cmbo = Me.Controls("Combobox" & i)
If rng = "Pet1" Then cmbo.RowSource = "details!c4:c24"
If rng = "Pet2" Then cmbo.RowSource = "details!e4:e24"
If rng = "Pet3" Then cmbo.RowSource = "details!g4:g24"
If rng = "Pet4" Then cmbo.RowSource = "details!i4:i24"
If rng = "Pet5" Then cmbo.RowSource = "details!k4:k24"
If rng = "Pet6" Then cmbo.RowSource = "details!m4:m24"
If rng = "Unplanned" Then cmbo.Value = "N/A"


j = j + 1
Next i

Essentially I need to;

Check a varable range and update a related combobox

So if rng = Sheets("sheet4").Range("d4").Value and d4 value = "Pet4"
then the rowsource of ComboBox70 would be "details!i4:i24" (as i would
= 70 and j would equal 4 and cmbo would equal ComboBox70)

The above code still errors in the same place as before. Thanks,

 

[anon]

I get runtime error 91, Object variable or With Block variable not set
on the line

cmbo = Me.Controls("Combobox" & j)

 

[anon]

It works - you're a genius and life saver!

I had to have the line

Set cmbo =

instead of

cmbo =

Thanks so much for your help!