Simple code, But whats wrong with it? <Resolved>

Can anyone help me here, i am trying to populate a combobox from excel using a range of numbers. I have the below code, however the list is not populating, and i know this is possible, just maybe not the way i'm doing it.

I just want it populated at runtime, so the user can select from the info contained in it.

'appExcel.Workbooks.Open FileName:="C:\Program Files\LFhost\Maccrystal01.xls"
appExcel.Worksheets("dblisting").Select
cmbbx.AddItem = appExcel.Range("a1:a255")


Additonally, is there a way to stop it from duplicating items? they can occur up to 19 times each. I only want a list of one of each value.


As always help is alway appreciated.
M

this may be off but do you do a .refresh after the .add?

Nop at this stage i dont, as the list is static and only gets modified about once every three months.

Which is why i thought only refresh it once at runtime and save needles looping.

Does that make sense?


M.

I don't think you can add the range, I think you need to add each item one at a time . . .

VB Code:

Dim c As appExcel.Range
    appExcel.Workbooks.Open FileName:="C:\Program Files\LFhost\Maccrystal01.xls"
    appExcel.Worksheets("dblisting").Select
    For Each c In range("a1:a255").Cells
        cmbbx.AddItem  c.Text
    Next c

Thanks for that,

Does this mean that it will add "all" values (c) to the list?

I can find another way to sort for duplicates.

or do i need to specify each item individually?


Thanks for all your help.

Nb: Yes very cute son, we have baby # 2 on the way in June next year.

Yes it will add duplicates also.

You could add the items to an array first, sort array, remove dups, and then populate combo.

And more importantly . . .

Congrats on baby #2 on the way!!

I don't plan on having any kids, cute baby though ^_^

I'm not too sure about arrays, as i'm only really starting out so will keep trouping and see if there is another way to do it, but thanks that worked a treat.

Thanks to all

You can even do it without an array . . .

VB Code:

Option Explicit
 
Sub LoadComboBox()
    
    Dim c As appExcel.range
    Dim blnDuplicateFound As Boolean
    Dim i As Integer
    
    appExcel.Workbooks.Open FileName:="C:\Program Files\LFhost\Maccrystal01.xls"
    appExcel.Worksheets("dblisting").Select
    
    'make sure you have a list view on your form
    'set the "sorted" property to "true" at design time
    'we don't even need to sort an array!
    'this is going to be a "behind-the-scenes" box so hide it
    'by setting visible = false at design time also
        
    lstSort.Clear
    For Each c In range("a1:a255").Cells
        lstSort.AddItem c.Text
    Next c
        
    'we need to check the list at least once
    blnDuplicateFound = True
    
    Do While blnDuplicateFound = True
        blnDuplicateFound = False
        For i = 0 To lstSort.ListCount - 1
            'ignore the first one!
            If i <> 0 Then
                If lstSort.List(i) = lstSort.List(i - 1) Then
                    'get rid of the duplicate
                    lstSort.RemoveItem (i - 1)
                    'we need to check the list again
                    blnDuplicateFound = True
                    Exit For
                End If
            End If
        Next i
    Loop
    
    'all the duplicates are gone, now let's add to the combo box
    cmbBx.Clear
    For i = 0 To lstSort.ListCount - 1
        cmbBx.AddItem lstSort.List(i)
    Next i
 
End Sub

You're trulty brillaint. Thanks youve saved me a heap of time.

Thanks Again

Hey man, glad I could help!


several months ago I was doing all the asking!