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

[Morpheus]

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

[Halon]

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

[Morpheus]

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.

[Armbruster]

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

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

[Morpheus]

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.

[Armbruster]

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

[scr0p]

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

[Morpheus]

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

[Armbruster]

You can even do it without an array . . .

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

[Morpheus]

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

Thanks Again

[Armbruster]

Hey man, glad I could help!


several months ago I was doing all the asking!