List Box filter based upon Checkbox selection

[chipsworld]

Hi all...Need some guidance. I have been researching this all over, but I can't seem to find anything that is remotely the same..

I am trying to filter my list box based upon the value in one column (Only two options)

My list box is populated from a sheet that continues to grow, hence the .End(xlUp).Row) portion and not a fixed range of rows.

I have been trying to figre out how to apply the .Autofilter control, but am having zero luck so far...

As you can see, I am only displaying certain columns in the list box. There are two list boxes, one for a header and one for the data lines. The filter only needs to apply to "rng"

Any help would be greatly appreciated.

Dim ws      As Worksheet
Dim rng As Range
Dim MyArray
Dim MyArray2 ' variant, receives one based 2-dim data field array
Dim rng2 As Range
Dim vData As Variant
Dim vTemp As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim SortByCol As Long

SortByCol = 15

Set ws = Sheets("Historical") 'set sheetname

' Set range here
Set rng = ws.Range("A2:AF" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row) [B].AutoFilter(Field:=3, Criteria:="USAR") ' this part returns error "Compile Error: Named Argument not found"[/B]

Set rng2 = ws.Range("A1:AF1")
With Me.lstlabel
.Clear
.ColumnHeads = False
    .ColumnCount = rng.Columns.Count

    'create a one based 2-dim datafield array
     MyArray = rng2

    'fill listbox with array values
    .List = MyArray

    'Set the widths of the column here.
.ColumnWidths = "60;150;100;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;100;0;0;150;0;0"
.TopIndex = 0
End With

With Me.lstboxchoice
.Clear
.ColumnHeads = False
    .ColumnCount = rng.Columns.Count

    'create a one based 2-dim datafield array
     MyArray2 = rng

    'fill listbox with array values
    .List = MyArray2

    'Set the widths of the column here.
.ColumnWidths = "60;150;100;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;100;00;0;150;0;0"
.TopIndex = 0
End With


With Me.lstboxchoice
vData = .List
For i = LBound(vData, 1) To UBound(vData, 1) - 1
For j = i + 1 To UBound(vData, 1)
If vData(i, SortByCol) < vData(j, SortByCol) Then
For k = LBound(vData, 2) To UBound(vData, 2)
vTemp = vData(i, k)
vData(i, k) = vData(j, k)
vData(j, k) = vTemp
Next k
End If
Next j
Next i
.List = vData
    End With

 

[chipsworld]

Sorry...My rng line should read...

Set rng = ws.Range("A2:AF" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).AutoFilter(Field:=2, Criteria1:="USAR") ' I am getting Runtime error 424 - Object Required

Also noticed that it is applying an actual filter to the sheet, not just the listbox...is this correct? If so, how would is un-filter it when done?

 

[chipsworld]

All...thank you but I figured it out on my own.

The secret was to put the filter code in the checkbox sub, not the listbox sub... O placed the same code in each checkbox with the filters changed for each separate checkbox.

Basically I was trying to filter the listbox contents based upon one of two check boxes. The below worked perfectly for my need...

The solution was some code I found on a different help page, that someone else wrote and I adapted to my need... I hope that someone else finds this useful. Again, this is only for a filter on a single column and one checkbox selected at a time. Not sure if it could be adapted to multiple check boxes??

Private Sub ckarng_Click()
If Me.ckarng.Value = True Then
Me.ckusar.Value = False
End If

Call UserForm_Activate ' resets the listbox for new filter

' filter listbox for only ARNG entries
Dim n, j As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")

lr = ws.Range("A" & Rows.Count).End(xlUp).Row

n = Me.lstboxchoice.ListCount - 1

If Me.ckarng.Value = True Then
For j = n To 0 Step -1 'filtering column 2 in listbox for all shown in the "OR" statement.
If Me.lstboxchoice.List(j, 2) = "USAR" Or Me.lstboxchoice.List(j, 2) = "USAR - REFRAD CHOICE" Or Me.lstboxchoice.List(j, 2) = "USAR - CHAPTER" _ 
Or Me.lstboxchoice.List(j, 2) = "AGR" Then
Me.lstboxchoice.RemoveItem j
End If
Next j
End If

End Sub