Setting and filter Listbox data

G

Gizmo63

Hi all,

I rarely use listboxes and this has got me foxed.

I have a user form where a PO number can be selected from a list
(fmPOmaint.cbPOnum). It is possible for any PO to have date conflicts and I
want to call another form that simply lists all the conflicts.

I have an array called po_conflicts (varaible number of rows x 6 columns)
which has pre stored any existing conflicts. I have used
lbConflicts.List = PO_conflicts
to show the array in the listbox but how can I filter it so that only the
required POnum is shown.

Is there something like:

lbConflicts.List = PO_conflicts Where PO_Conflicts( column 0.value) =
fmPOmaint.cbPOnum

Cheers all.

Giz
 
D

Dave Peterson

You can loop through your range looking for matches. When you find one, you can
add it to the listbox.

But it kind of looks like .cbPOnum is really a combobox. Maybe you can modify
this to do what you want:

Option Explicit
Private Sub UserForm_Initialize()

Me.ComboBox1.List = Worksheets("POs").Range("a1:A10").Value
Me.ListBox1.RowSource = ""
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 6

End Sub
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long

Me.ListBox1.Clear
If Me.ComboBox1.ListIndex > -1 Then
With Worksheets("conflicts")
Set myRng = .Range("a1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

For Each myCell In myRng.Columns(1).Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
With Me.ListBox1
.AddItem myCell.Value
For iCtr = 1 To 5
.List(.ListCount - 1, iCtr) _
= myCell.Offset(0, iCtr).Value
Next iCtr
End With
End If
Next myCell
End If

End Sub

If you really meant an array, you'll need to modify this.
 
Top