Filling Combo Box with data from multiple cells 2

[ReportMaster]

I am trying to fill a combo box in excel with a data range from another sheet in the workbook. I have one combo box, say with a list of choices from 1-5. I want the second combo box to display multiple values from a range of cells in another sheet in the workbook based on the selection from combo box 1.

ex. pick 1 from combo box 1, need to populate combo box 2 with cells a1-a10 from sheetX

ex 2. pick 4 from combo box 1, need to populate combo box 2 with cells c4-c7 and cell c10 from sheetX

thanks for help in advance!!

 

[SkipVought]

Hi,

select case combobox1.listindex
  case 0
    set rng = sheets("sheetx").range("A1:A10")
  case 1
    set rng = application.union(sheets("sheetx").range("c4:c7"), sheets("sheetx").range("c10"))
...
end select
combobox2.clear
for each r in rng
   combobox2.additem r.value
next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ReportMaster]

Skip, thank you, your code was very helpful. It ran fine when I had it inside of a worksheet, but I have made some changes and it now lies inside of a user form. The first combo box populates correctly, but an error occurs when the second box tries to fill.

it points to this line of code:
for each r in rng

and the message is "for each control variable must be variant or object"

I tried declaring r as a string, but it didn't work. Thanks for helping!!!

 

[SkipVought]

r must be declared as Range



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ReportMaster]

Skip, I declared r as a Range, but now another eror is occuring telling me that an "object required". It points to the same line of code as before, any suggestions??

tnaks for all your help, RM

 

[SkipVought]

how is rng declared and assigned?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ReportMaster]

Skip, I have rng declared as a range. Here is my code:

Private Sub cboPrimary_change()
Dim r As Range
Dim rng As Range

Select Case cboPrimary.ListIndex
Case 0
Set rng = Sheet4.Range("g5:g8")
Case 1
Set rng = Sheet4.Range("g11:g16")
Case 2
Set rng = Sheet4.Range("g19:g26")
Case 3
Set rng = Sheet4.Range("g29:g32")
Case 4
Set rng = Sheet4.Range("g35:g39")
Case 5
Set rng = Sheet4.Range("k5:k23")
Case 6
Set rng = Sheet4.Range("k26:k50")
End Select

cboSecondary.Clear

For Each r In rng
cboSecondary.AddItem r.Value
Next

End Sub


Thanks for all your help Skip! -RM

 

[SkipVought]

I dunno???

It runs for me. I am using a combobox on sheet1

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

What is the value of cboPrimary.ListIndex when you raise the error ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884