Fill List Box With Data From Two Or More Columns

[Spaggiari]

Hi all, I've been working on a project and I'm having some trouble with filling a listbox with data from two or more columns from a sheet.

This is the code I used to fill a listbox with one column only. Now I need to fill a listbox with two columns and another one with five columns.

Private Sub UserForm_Initialize() 
     
    txtOperator.SetFocus 
    Dim aCell As Range 
    Worksheets("database").Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
    Application.ScreenUpdating = False 
     
    With Worksheets("database") 
        For Each aCell In .Range("Operators") 
            If aCell.Value <> "" Then 
                Me.lstOperators.AddItem aCell.Value 
            End If 
        Next 
    End With 
     
    Application.ScreenUpdating = True 
     
End Sub

Here's the link to the project so its easier for you to understand what I need https://www.dropbox.com/s/qb2m9a7a6a...project.xlsm?m

Cross-post links:

http://www.vbforums.com/showthread.p...53#post4220053
http://www.ozgrid.com/forum/showthre...d=1#post623751
http://www.vbaexpress.com/forum/show...759#post274759
http://www.xtremevbtalk.com/showthread.php?t=324615

Ty in advance.

[vbfbryce]

An example for 2 columns:

With LB     'my listbox
        .AddItem
        .list(0, 0) = "abc" 'substitute your range, first column, the first 0 is the list box index
        .list(0, 1) = "def" 'substitute your range, second column
    End With

[Spaggiari]

Ok so I have tried this solution I found in another forum and it fills the list box still I get duplicates.

Private Sub UserForm_Initialize()

Dim aCell As Range
txtCode.SetFocus
Application.ScreenUpdating = False

    With Worksheets("database")
        For Each aCell In .Range("Events")
            If aCell.Value <> "" Then
                Me.lstEvents.AddItem aCell.Value
                Me.lstEvents.List(Me.lstEvents.ListCount - 1, 1) = aCell.Offset(0, 1).Value
                Me.lstEvents.List(Me.lstEvents.ListCount - 1, 2) = aCell.Offset(0, 2).Value
            End If
        Next
    End With

Application.ScreenUpdating = True
     
End Sub

What is wrong with the code?

[vbfbryce]

I don't see where you're doing anything that would prevent duplicates. Looks like you're loading every non blank value.

[westconn1]

and it fills the list box still I get duplicates.

this was not part of the original question, for this case you would need to check the values in 1 or both columns to find if it already exists in the new list, or use a collection in between to prevent duplicates
as you have not been clear which column you want no duplicates, it is hard to propose a suitable solution

[Spaggiari]

In my file I have a userform Operators with a listbox and I fill the listbox using this code. I wanted to do the same thing but for 2 columns, this is working fine it may not be the fastest way but it works it loads all the values in the range wich is a dynamic named ranged. Am I beeing clear enough about this? Can it be done like this example?

Private Sub UserForm_Initialize()

txtOperator.SetFocus
Dim aCell As Range
Worksheets("database").Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.ScreenUpdating = False

    With Worksheets("database")
        For Each aCell In .Range("Operators")
            If aCell.Value <> "" Then
                Me.lstOperators.AddItem aCell.Value
            End If
        Next
    End With

Application.ScreenUpdating = True
           
End Sub

[westconn1]

well you can set the sourcerow for listbox, to the appropriate range