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.

[vba]
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


[/vba]

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

Ty in advance.

[Bob Phillips]

[VBA]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
Me.lstOperators.List(Me.lstOperators.ListCount - 1, 1) = aCell.Offset(0, 1).Value
'etc
End If
Next
End With

Application.ScreenUpdating = True

End Sub[/VBA]

[Spaggiari]

[vba]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
Me.lstOperators.List(Me.lstOperators.ListCount - 1, 1) = aCell.Offset(0, 1).Value
'etc
End If
Next
End With

Application.ScreenUpdating = True

End Sub[/vba]

I'll check it out, thx

[Spaggiari]

[vba]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
Me.lstOperators.List(Me.lstOperators.ListCount - 1, 1) = aCell.Offset(0, 1).Value
'etc
End If
Next
End With

Application.ScreenUpdating = True

End Sub[/vba]

The code works but I have duplicates on the listbox, I tried to load the listbox in the userform events with this code but I get Event Description on duplicate.
[vba]

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

[/vba]

[Bob Phillips]

You mean that you have two Userform_Initializze procedures? Can't do that.

[Spaggiari]

You mean that you have two Userform_Initializze procedures? Can't do that.

Nope I only have one userform_initiliaze procedure i mistyped.

[Bob Phillips]

so what exactly is the problem?

[snb]

To populate a listbox with 2 columns:

[vba]Listbox1.List=sheet1.cells(1).currentregion.resize(,2).value[/vba]

To populate a listbox with 5 columns:

[vba]
Listbox1.List=sheet1.cells(1).currentregion.resize(,5).value
[/vba]

[Spaggiari]

so what exactly is the problem?

The problem is that I'm getting duplicates on the listbox as I said before.

Cheers

[Spaggiari]

To populate a listbox with 2 columns:

[vba]Listbox1.List=sheet1.cells(1).currentregion.resize(,2).value[/vba]

To populate a listbox with 5 columns:

[vba]
Listbox1.List=sheet1.cells(1).currentregion.resize(,5).value
[/vba]

I'll try it out and post results later.

Ty

[snb]

If you want to omit duplicates in a 2-column range:

[VBA]
Sub snb_002()
Cells(1).CurrentRegion.resize(,2).AdvancedFilter 2, , Cells(1, 10), True
ListBox1.List = Cells(1, 10).CurrentRegion.resize(,2).Value
Cells(1, 10).CurrentRegion.resize(,2).ClearContents
End Sub
[/VBA]

in a 5-column range:
[VBA]
Sub snb_005()
Cells(1).CurrentRegion.resize(,5).AdvancedFilter 2, , Cells(1, 10), True
ListBox1.List = Cells(1, 10).CurrentRegion.resize(,5).Value
Cells(1, 10).CurrentRegion.resize(,5).ClearContents
End Sub
[/VBA]

[Spaggiari]

In my file > here 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 this be done like the example I gave you?

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

[Spaggiari]

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