ComboBox expert help needed!!

Hi all

I'm trying to make a new reporting tool, and bounced into trouble right away.

So far I have a datasheet (huge), and a starting page. On this starting page I have 5 comboboxes, default names are used, so they are called combobox1 to 5.

I want the list in the combobox1 to 5, to consist of column a to e on the data sheet (about 30000 rows, but only 15 different valules).

My code is;

VB Code:

Private Sub workbook_open()
Dim sSheet As Worksheet
Dim dSheet As Worksheet
Dim aArray As Variant
Dim bArray As Variant
Dim cArray As Variant
Dim dArray As Variant
Dim eArray As Variant
Dim i As Integer
 
Set sSheet = ActiveWorkbook.Sheets("Start")
Set dSheet = ActiveWorkbook.Sheets("data")
 
With ActiveWorkbook.dSheet
        aArray = .Range(.Cells(2, 1), .Cells(65000, 1))
        bArray = .Range(.Cells(2, 2), .Cells(65000, 2))
        cArray = .Range(.Cells(2, 3), .Cells(65000, 3))
        dArray = .Range(.Cells(2, 4), .Cells(65000, 4))
        eArray = .Range(.Cells(2, 5), .Cells(65000, 5))
End With
 
For i = LBound(aArray) To UBound(aarray)
    sSheet.combobox1.AddItem aarray(i)
Next i
 
End Sub

I've also tried using listfillrange - but this rendered me with the same result;

"Compile Error: Method or data member not found"

What am I missing??

Okay - for some odd reason

I got it to work with listfillrange. However I still have a problem. As it works right now, it takes all of the instances in the chosen column - for instance if the word; ireland is present 700 times in the column, then the list in my combobox will read ireland 700 times - naturally I only want a single instance??

How is this done? So far I only got:

VB Code:

Private Sub workbook_open()
 
Sheet1.combobox1.ListFillRange = "=data!A2:A65000"
 
End Sub

any suggestions?