Populating Combo box (.List) using a Range??

  • Thread starter Thread starter Rob W
  • Start date Start date
R

Rob W

Greetings,

Is it possible to populate a combobox.List property with a Range?
I know its common practice to use Array, wondering about a Range.

I've experimented with :-

Dim R As Range
Set R =
Sheets("Data").Columns("E").SpecialCells(xlCellTypeBlanks).Offset(, -4)

comboPatient.List = R.Value or
comboPatitint.List = Range("R").value

and all give syntax errors ..

Thanks
Rob
 
You can use a range ok, but the problem is that your range has (probably)
multi-areas. You could loop through the cells and use .additem, though.
 
Thanks it is multiple areas, Ive used a loop to achieve the population of a
combo box.

With comboPatient
For Each cell In
Sheets("Data").Columns("E").SpecialCells(xlCellTypeBlanks).Offset(, -4)
.AddItem CStr(cell.Value)
Next cell
End With

If you can think of a more efficent way, I would appreciate it.

Thanks again
Rob
 
That's what I'd use.

Rob said:
Thanks it is multiple areas, Ive used a loop to achieve the population of a
combo box.

With comboPatient
For Each cell In
Sheets("Data").Columns("E").SpecialCells(xlCellTypeBlanks).Offset(, -4)
.AddItem CStr(cell.Value)
Next cell
End With

If you can think of a more efficent way, I would appreciate it.

Thanks again
Rob
Click to expand...
 
On topic but on a tiny tangent.. Updating .List with a collection (see code
below which adds data to a collection)

Dim Uniques As New Collection

For Each cell In wsData.Range("G2:G" &
lRowEnd).SpecialCells(xlCellTypeConstants, 2)
Uniques.Add cell.Value, CStr(cell.Value)
Next cell

'Bubble sort code ommitted

For Each Item In Uniques
'comboDiagnosis.AddItem Item
Next Item

I want to add these to the combobox.List I've had several failed attempts..

comboDiagnosis.List = Application.Transpose(Uniques)

Error 1004 appeared 'Application.defined or Object.defined error'

Maybe I need to convert the items/collection into something else to allow it
to be added??
 
You could loop through the collection and create an array and use that in the
..list assignment.

Rob said:
On topic but on a tiny tangent.. Updating .List with a collection (see code
below which adds data to a collection)

Dim Uniques As New Collection

For Each cell In wsData.Range("G2:G" &
lRowEnd).SpecialCells(xlCellTypeConstants, 2)
Uniques.Add cell.Value, CStr(cell.Value)
Next cell

'Bubble sort code ommitted

For Each Item In Uniques
'comboDiagnosis.AddItem Item
Next Item

I want to add these to the combobox.List I've had several failed attempts..

comboDiagnosis.List = Application.Transpose(Uniques)

Error 1004 appeared 'Application.defined or Object.defined error'

Maybe I need to convert the items/collection into something else to allow it
to be added??
Click to expand...
 
Back
Top