Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update numerous comboxes at once
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update numerous comboxes at once
Comboboxes on a userform?
dim i as long for i = 4 to 34 me.controls("Combobox" & i).rowsource _ = worksheets("details").range("C4:c24").address(exte rnal:=true) next i anon wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update numerous comboxes at once
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update numerous comboxes at once
I get runtime error 91, Object variable or With Block variable not set
on the line cmbo = Me.Controls("Combobox" & j) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update numerous comboxes at once
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding Rows and buttons/comboxes, over the rows | Excel Programming | |||
How do you add numerous subtotals in a spreadsheet? | Excel Worksheet Functions | |||
Macro to make all checkboxes false and clear all comboxes | Excel Discussion (Misc queries) | |||
Comboxes | Excel Programming | |||
Need to sum up numerous columns in different worksheet into 1 | New Users to Excel |