Hey guys,

i previously put a post up about optimising a search feature in a piece of
VBA i was writting, but i have decided against this now as it would just take
up too much time. my new idea of getting around this problem is to take all
the values in a column, pick out 1 instance of each (Via a collection) and
then out put that collection to a drop down menu/Combo Box. this is the code
i have so far, i am just unsure about how to get the collection data into the
combo box.

Sub DropDown()

Dim Cell As Range
Dim Rng As Range
Dim ListEntry As Collection

Sheet1.ComboBoxEntry.Clear

Application.ScreenUpdating = False

Set Rng = Sheets("Data").Range("D:D")

On Error Resume Next
For Each Cell In Rng
ListEntry.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In ListEntry 'I didnt expect this line to work, just added it
to illustrate what i am after.
Sheets("Summary").ComboBoxEntry.AddItem
Next Item

Application.ScreenUpdating = True

End Sub

i have put this piece of code on the "ThisWorkbook" area of the VBA
enviroment, i have had problems with combo boxes in other work books when i
put code in the sheet the box was on.

any help would be very welcome

The Noob

Nevermind, problem solved.

The Noob

"Noob McKnownowt" wrote:

Hey guys,

i previously put a post up about optimising a search feature in a piece of
VBA i was writting, but i have decided against this now as it would just take
up too much time. my new idea of getting around this problem is to take all
the values in a column, pick out 1 instance of each (Via a collection) and
then out put that collection to a drop down menu/Combo Box. this is the code
i have so far, i am just unsure about how to get the collection data into the
combo box.

Sub DropDown()

Dim Cell As Range
Dim Rng As Range
Dim ListEntry As Collection

Sheet1.ComboBoxEntry.Clear

Application.ScreenUpdating = False

Set Rng = Sheets("Data").Range("D:D")

On Error Resume Next
For Each Cell In Rng
ListEntry.Add Cell.Value, CStr(Cell.Value)
Next Cell

For Each Item In ListEntry 'I didnt expect this line to work, just added it
to illustrate what i am after.
Sheets("Summary").ComboBoxEntry.AddItem
Next Item

Application.ScreenUpdating = True

End Sub

i have put this piece of code on the "ThisWorkbook" area of the VBA
enviroment, i have had problems with combo boxes in other work books when i
put code in the sheet the box was on.

any help would be very welcome

The Noob