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

ouestque
ouestque used Ask the Experts™
on
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.")
10-8-10-Experts-Exchange-Example.xls
Comment
Watch Question

Do more with

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

Here is a workaround.

Kris
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()
    RefreshAdvFilter
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = RowSourceAddr
    End With
End Sub

Private Sub UserForm_Initialize()
    
    RefreshAdvFilter
    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