PDA

View Full Version : [SOLVED] Listbox problem



gibbo1715
08-30-2005, 02:24 AM
i have a userform with a list box to display items on a sheet as follows


With ThisWorkbook.Sheets("Items")
If Range("A3").value = "" Then
LastRow = Range("3")
Else
LastRow = .Range("A65536").End(xlUp).Row
End If
frmItems.ListBox1.RowSource = .Range("A3:D" & LastRow).Address
End With

which works fine if i want to see all the items.

but what i now need is only to list items where the value in the F column = y
( or activecell.offset(0,5) = y if you prefer)

Can anyone help

Many thanks

Gibbo

Bob Phillips
08-30-2005, 02:31 AM
You would need to do one of two things


filter the data into another range, filter on column F, and link to that
loop through the range and load the listbox manually

gibbo1715
08-30-2005, 02:33 AM
any chance of an example of the second method please

Bob Phillips
08-30-2005, 03:33 AM
any chance of an example of the second method please

Sure.



Dim LastRow As Long
Dim i As Long
Dim oLB As Object
Set oLB = frmItems.ListBox1
With ThisWorkbook.Sheets("Items")
If Range("A3").Value = "" Then
LastRow = Range("3")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End If
For i = 3 To LastRow
If Cells(i, "F").Value = "y" Then
oLB.AddItem Cells(i, "A")
oLB.List(oLB.ListCount - 1, 1) = Cells(i, "B")
oLB.List(oLB.ListCount - 1, 2) = Cells(i, "C")
oLB.List(oLB.ListCount - 1, 3) = Cells(i, "D")
End If
Next i
End With

gibbo1715
08-30-2005, 03:40 AM
Absolutely perfect

I owe you my thanks again

Gibbo

Bob Phillips
08-30-2005, 05:25 AM
Can you mark it solved then?