Show filtered data in listbox on a userform

**kammend** · 12-09-2021, 04:48 AM

Hello,

In a worksheet I have a list with different kinds or records (errors, warnings, etc). I also have a userform that can show these. What I want to accomplish is that I can use a optionbutton to filter 1 type of record and show that in that listbox of my userform. See attached.

I am able to show all data in the listbox. And I am also able to filter the data in the table. However, I cannot get only the filtered data in the listbox.

Private Sub OptionErrors_Click()

Dim wsw As Worksheet
Set wsw = Worksheets("Warnings")
wsw.Range("ErrMsg_Filter").Value = "Err"

Dim i As Integer   ' row in Warning ws
i = 5                 ' first row

Dim LastRow As Long

LastRow = wsw.Range("A" & Rows.Count).End(xlUp).Row   ' Last row in Warnings ws
If LastRow < 5 Then LastRow = 5

Dim lo As ListObject
Set lo = wsw.ListObjects(1)
lo.AutoFilter.ShowAllData

With lo.Range
    .AutoFilter Field:=6, Criteria1:="E"
End With

Dim rngWarnings As Range
Set rngWarnings = lo.DataBodyRange.Columns("B:F")

With Me.ListBox1
    .ColumnWidths = "30;50;325;450"
    .ColumnCount = -1
    .List = rngWarnings.Cells.Value
End With

End Sub

**Sintek** · 12-09-2021, 05:52 AM

Replace your code with this...
Have only done Errors & Warnings buttons

Option Explicit

Private Crit As String

Private Sub UserForm_Initialize()
Dim Data
With Sheets("Warnings").ListObjects(1).Range
    Data = Application.Index(.Value, Application.Evaluate("row(1:" & .Rows.Count & ")"), Array(2, 3, 4, 5))
    With Me.ListBox1
        .ColumnWidths = "30;50;325;450"
        .ColumnCount = -1
        .List = Data
    End With
End With
End Sub
Private Sub OptionErrors_Click()
Crit = "E"
LbxPop
End Sub
Private Sub OptionWarnings_Click()
Crit = "W"
LbxPop
End Sub
Private Sub LbxPop()
Dim Data, Arr
With Sheets("Warnings").ListObjects(1).Range
    Data = Filter(Evaluate("transpose(if(" & .Columns(6).Address & "=""" & Crit & """,row(1:" & .Rows.Count & ")))"), False, 0)
    If UBound(Data) > -1 Then
        Arr = Application.Index(.Value, Application.Transpose(Data), Array(2, 3, 4, 5))
        With Me.ListBox1
            .ColumnWidths = "30;50;325;450"
            .ColumnCount = 4
            .List = Arr
        End With
    End If
End With
End Sub

**kammend** · 12-09-2021, 06:11 AM

Hi Sintek,

I made a few small changes and it does what I need.
Thanks for helping me in the right direction :-)

one question in your code..

Dim Data, Arr

why do you not define the variables as a type?
e.g. Dim Data as Range, Arr as Array, etc

**Sintek** · 12-09-2021, 06:15 AM

If it is not specified it automatically takes on data type Variant
Above is same as...

Dim Data As Variant, Arr As Variant

**kammend** · 12-09-2021, 06:29 AM

in my mastersheet I call this userform form another worksheet. Then the filtering does not work.
So, when I move the CommandButton to a second sheet (in my example file) I see this same behavior. Why would that be? Is that because the filter is applied to the active sheet?

**Sintek** · 12-09-2021, 06:30 AM

Add the red snippet in Private Sub LbxPop()

Data = Filter(.Parent.Evaluate("transpose(if(" & .Columns(6).Address & "=""" & Crit & """,row(1:" & .Rows.Count & ")))"), False, 0)

**kammend** · 12-09-2021, 06:33 AM

I have moved the CommandButton1 to "Sheet1"

**kammend** · 12-09-2021, 06:39 AM

That did it :-)
Problem solved. :-)

**kammend** · 12-09-2021, 06:45 AM

There is still one tiny issue with the code... When I only have 1 line in the table, then the alignment of the columns is messed up.. :-(

**Ali-M** · 12-09-2021, 06:52 AM

@Kammend did you test optionbutton(Both Errors and Warnings) it doesn't seem to work. it should show all of data as when run the userform from the first time

**Sintek** · 12-09-2021, 06:54 AM

Here...is amended if only 1 row entry...

**Sintek** · 12-09-2021, 07:13 AM

For both...use code in file above and this snippet

Private Sub OptionBoth_Click()
Crit = """<>"""
LbxPop
End Sub

**kammend** · 12-09-2021, 09:28 AM

yes, it now works as I want it. Thanks

**torachan** · 12-09-2021, 09:06 AM

Variation on a theme.

**Artik** · 12-09-2021, 10:03 AM

And one more variation on a theme. The solution uses Recordset.

I checked all three examples for 100k records. They all managed in an acceptable time.

Artik

**Sintek** · 12-09-2021, 10:34 AM

Simplification....

Show filtered data in listbox on a userform

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Re: Show filtered data in listbox on a userform

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Populate UserForm ListBox with unique values from filtered data

[SOLVED] Want to Show Filtered data in Listbox with Tickbox Option to Filter data

[SOLVED] Want to Show Filtered data in Listbox with Tickbox Option to Filter data

[SOLVED] How to filter a table using textbox and show filtered data in listbox (userform)

[SOLVED] How to show data in UserForm ListBox from WorkSheet?

Userform to show filtered data

Using Filtered Data in Userform Listbox

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions