Setting and filter Listbox data

[Guest]

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

 

[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.