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(xlCellTyp eBlanks).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.

Rob W wrote:
>
> 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(xlCellTyp eBlanks).Offset(, -4)
>
> comboPatient.List = R.Value or
> comboPatitint.List = Range("R").value
>
> and all give syntax errors ..
>
> Thanks
> Rob

--

Dave Peterson

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(xlCellTyp eBlanks).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


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:47570640.2DBC26CC@verizonXSPAM.net...
> 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.
>
> Rob W wrote:
>>
>> 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(xlCellTyp eBlanks).Offset(, -4)
>>
>> comboPatient.List = R.Value or
>> comboPatitint.List = Range("R").value
>>
>> and all give syntax errors ..
>>
>> Thanks
>> Rob
>
> --
>
> Dave Peterson

That's what I'd use.

Rob W wrote:
>
> 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(xlCellTyp eBlanks).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
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:47570640.2DBC26CC@verizonXSPAM.net...
> > 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.
> >
> > Rob W wrote:
> >>
> >> 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(xlCellTyp eBlanks).Offset(, -4)
> >>
> >> comboPatient.List = R.Value or
> >> comboPatitint.List = Range("R").value
> >>
> >> and all give syntax errors ..
> >>
> >> Thanks
> >> Rob
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4757106E.2879D32E@verizonXSPAM.net...
> That's what I'd use.
>
> Rob W wrote:
>>
>> 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(xlCellTyp eBlanks).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
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:47570640.2DBC26CC@verizonXSPAM.net...
>> > 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.
>> >
>> > Rob W wrote:
>> >>
>> >> 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(xlCellTyp eBlanks).Offset(, -4)
>> >>
>> >> comboPatient.List = R.Value or
>> >> comboPatitint.List = Range("R").value
>> >>
>> >> and all give syntax errors ..
>> >>
>> >> Thanks
>> >> Rob
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

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

Rob W wrote:
>
> 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??
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4757106E.2879D32E@verizonXSPAM.net...
> > That's what I'd use.
> >
> > Rob W wrote:
> >>
> >> 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(xlCellTyp eBlanks).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
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:47570640.2DBC26CC@verizonXSPAM.net...
> >> > 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.
> >> >
> >> > Rob W wrote:
> >> >>
> >> >> 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(xlCellTyp eBlanks).Offset(, -4)
> >> >>
> >> >> comboPatient.List = R.Value or
> >> >> comboPatitint.List = Range("R").value
> >> >>
> >> >> and all give syntax errors ..
> >> >>
> >> >> Thanks
> >> >> Rob
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson