Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting and filter Listbox data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting and filter Listbox data
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. Gizmo63 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consulting Access from Excel | Excel Discussion (Misc queries) | |||
Automatic filter during import of external data? | Excel Discussion (Misc queries) | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Custom Auto Filter default setting should be contains | Excel Worksheet Functions |