PDA

View Full Version : Fill List Box With Data From Two Or More Columns



Spaggiari
08-17-2012, 02:28 AM
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 (https://www.dropbox.com/s/qb2m9a7a6at7e68/project.xlsm?m)

Ty in advance.

Bob Phillips
08-17-2012, 03:43 AM
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

Spaggiari
08-17-2012, 04:08 AM
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

I'll check it out, thx

Spaggiari
08-17-2012, 04:55 AM
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

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.


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

Bob Phillips
08-17-2012, 05:27 AM
You mean that you have two Userform_Initializze procedures? Can't do that.

Spaggiari
08-17-2012, 05:54 AM
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
08-17-2012, 09:34 AM
so what exactly is the problem?

snb
08-18-2012, 06:17 AM
To populate a listbox with 2 columns:

Listbox1.List=sheet1.cells(1).currentregion.resize(,2).value

To populate a listbox with 5 columns:


Listbox1.List=sheet1.cells(1).currentregion.resize(,5).value

Spaggiari
08-20-2012, 02:37 AM
so what exactly is the problem?

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

Cheers

Spaggiari
08-20-2012, 02:38 AM
To populate a listbox with 2 columns:

Listbox1.List=sheet1.cells(1).currentregion.resize(,2).value

To populate a listbox with 5 columns:


Listbox1.List=sheet1.cells(1).currentregion.resize(,5).value


I'll try it out and post results later.

Ty

snb
08-20-2012, 03:16 AM
If you want to omit duplicates in a 2-column range:


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


in a 5-column range:

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

Spaggiari
08-20-2012, 04:12 AM
In my file > here (http://www.vbaexpress.com/forum/www.dropbox.com/s/qb2m9a7a6at7e68/project.xlsm?m) 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
08-20-2012, 06:22 AM
Cross-post links:
http://www.vbforums.com/showthread.php?687803-Fill-List-Box-With-Data-From-Two-Or-More-Columns&p=4220053#post4220053
http://www.ozgrid.com/forum/showthread.php?t=168798&p=623751&posted=1#post623751
http://www.vbaexpress.com/forum/showthread.php?p=274759#post274759
http://www.xtremevbtalk.com/showthread.php?t=324615