Excel 2003: Listbox control source (Only show visible rows.)

ouestque used Ask the Experts™
See attached Excel file.

This attached Excel file contains a form with a listbox on it. (You can open this form by clicking on the "Open Form" button.)

Notice that the listbox is populated because the "RowSource" of the listbox equals a named range. (i.e. in this case "Names2")

How do I make only visible rows show in my listbox.
(i.e. if I hide a couple rows, then click the "Refresh" button on the form, the hidden rows won't show.)

Is there a way to do this without using VBA to paste data to another sheet? (i.e. maybe there is a feature in the listbox that says: "Only show the visible rows in the named range.")
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Here is a workaround.

In a standard module

Public RowSourceAddr    As String
Sub RefreshAdvFilter()
    Dim CriteriaRange   As Range
    Dim wksTemp         As Worksheet
    Dim DataRange       As Range
    Dim wksActive       As Worksheet
    Dim LastCol         As Long
    Dim LastRow         As Long
    Set wksActive = ActiveSheet
    Const CriteriaCol   As Long = 3 '<<== adjust this column #
    Const StatusCol     As Long = 2 '<<== adjust this column #
    Set DataRange = wksActive.Range("a1").CurrentRegion.Resize(, StatusCol)
    Set CriteriaRange = wksActive.Cells(1, CriteriaCol).Resize(2)
    CriteriaRange.Cells(2, 1).FormulaR1C1 = "=or(rc[-" & CriteriaCol - StatusCol & "]={""Failed"",""Passed""})"
    On Error Resume Next
    Set wksTemp = Worksheets("TempData")
    On Error GoTo 0
    If wksTemp Is Nothing Then
        Set wksTemp = Worksheets.Add
        wksTemp.Name = "TempData"
    End If
    DataRange.AdvancedFilter xlFilterCopy, CriteriaRange, wksTemp.Range("A1"), False
    With wksTemp.Range("a1")
        LastCol = .CurrentRegion.Columns.Count
        LastRow = .CurrentRegion.Rows.Count
        .CurrentRegion.Value2 = .CurrentRegion.Value2
        RowSourceAddr = .Offset(1).Resize(LastRow - 1, LastCol).Address(, , , 1)
    End With
    Set wksActive = Nothing
    Set wksTemp = Nothing
    Set DataRange = Nothing
    Set CriteriaRange = Nothing
End Sub

In userform module

Private Sub Refresh_Click()
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = RowSourceAddr
    End With
End Sub

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = RowSourceAddr
    End With
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial