Listbox problem

[gibbo1715]

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]

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]

any chance of an example of the second method please

[Bob Phillips]

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]

Absolutely perfect

I owe you my thanks again

Gibbo

[Bob Phillips]

Can you mark it solved then?