Combo box source data

[Kelethaar]

I've been struggling with this one for a while now... I am trying (futily) to get a combo box to display from a single row, omitting duplicates and blanks. much like the auto filter combo boxes (that M$ provided with excel). I'm sure it can be done, but I'm at a loss, and I can't seem to find the answer in as many combobox posts that i can find

office 2k3 and the combo box is within UserForm1. I want to pull the data from Column A (is there a column index in 2K3???) on worksheets(1).

any advice is appreciated.

Thanks,

 

[dfenton21]

Hi There,

If you add the code below to your form's initialize event, you should get the desired result. I've added comments to explain my code.

I'm assuming your combobox is called combobox1. Please change as required.

If you experience problems, or don't understand the code, please let me know and I'll be happy to assist.

Good luck.

Private Sub UserForm_Initialize()
    
    Dim vntItem As Variant
    Dim iLastCol As Integer
    Dim bIsDuplicate As Boolean
    
    'get column number of last occupied cell in row 1 (change row number as required)
    iLastCol = Range("IV1").End(xlToLeft).Column
    
    'loop though cells in row (change row number as required)
    For Each cell In Range(Cells(1, 1), Cells(1, iLastCol))
        
        'if combobox is empty, add the first item
        If ComboBox1.ListCount = 0 Then
            ComboBox1.AddItem (cell.Value)
        Else
            
            'ignore blanks
            If cell.Value <> vbNullString Then
            
                'assume item is not a duplicate (set duplicate flag to false)
                bIsDuplicate = False
            
                'loop though each item currently in the combobox, and compare it with cell.value
                'if a match is found, set the duplicate flag to true
                For Each vntItem In ComboBox1.List
                    If cell.Value = vntItem Then
                       bIsDuplicate = True
                    End If
                Next vntItem
                
                'add item to combobox if item's duplicate flag is false
                If bIsDuplicate = False Then
                    ComboBox1.AddItem (cell.Value)
                End If
        
            End If
        
        End If
        
    Next cell
    
End Sub

 

[dfenton21]

I reread your post, and I think i misread it.
When you said "display from a single row" I assumed the combobox source items are in row1 spread across multiple columns. However, I think you meant they are all in column A.

That is actually a little easier as we don't need a column number. Change the first 7 lines to this:

    Dim vntItem As Variant
    Dim bIsDuplicate As Boolean

    'loop though cells in column
    For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)

From 'if combobox is empty, add the first item, continue as above

 

[Kelethaar]

I think both will be invaluable for me, thank you, very much!

Kelethaar