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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob W - 05 Dec 2007 19:38 GMT

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

Reply to this Message

Dave Peterson - 05 Dec 2007 20:12 GMT

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.

> Greetings,
>
[quoted text clipped - 14 lines]
> Thanks
> Rob

Signature

Dave Peterson

Reply to this Message

Rob W - 05 Dec 2007 20:23 GMT

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

> 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.
[quoted text clipped - 17 lines]
>> Thanks
>> Rob

Reply to this Message

Dave Peterson - 05 Dec 2007 20:56 GMT

That's what I'd use.

> Thanks it is multiple areas, Ive used a loop to achieve the population of a
> combo box.
[quoted text clipped - 36 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rob W - 05 Dec 2007 22:56 GMT

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??

> That's what I'd use.
>
[quoted text clipped - 41 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 05 Dec 2007 23:48 GMT

You could loop through the collection and create an array and use that in the
.list assignment.

> On topic but on a tiny tangent.. Updating .List with a collection (see code
> below which adds data to a collection)
[quoted text clipped - 70 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message