I have a listbox1 which populates a second listbox2 upon selecting an item from the first listbox. I am trying to come up with a code for listbox2 to filter the data in my excel spreadsheet based upon the selection. However, I am new to vba and I am struggling because:
- my code for listbox2 is not filtering data and
- as of right now the code is static for one column.
In order for my code to work it needs to be dynamic in determining want column to filter. I added my code below:
- A) Initialize
- B) Listbox1 Click
- C)Listbox2 Click
Section C is where my code fails
Section A
Userform initialize:
Public Sub UserForm_Initialize()
With ListBox1
.AddItem "State"
.AddItem "County"
.AddItem "City"
End With
ListBox1.Font.Size = 12
ListBox1.Font.Name = "Arial"
End Sub
Section B
Listbox 1 Click
Private Sub ListBox1_Click()
Dim x As Integer
x = ListBox1.ListIndex
Select Case x
Case Is = 0
'code to add a unique list to user form when selecting budim yer
'With ListBox2
'
' .AddItem "Michael"
' .AddItem "Kassie"
'End With
Dim myCollection As Collection, cell As Range
On Error Resume Next
Set myCollection = New Collection
With ListBox2
.Clear
For Each cell In Range("O11:O6000")
If Len(cell) <> 0 Then
Err.Clear
myCollection.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
Case Is = 1
Dim my2Collection As Collection, cell_2 As Range
On Error Resume Next
Set my2Collection = New Collection
With ListBox2
.Clear
For Each cell_2 In Range("D11:D6000")
If Len(cell_2) <> 0 Then
Err.Clear
my2Collection.Add cell_2.Value, cell_2.Value
If Err.Number = 0 Then .AddItem cell_2.Value
End If
Next cell_2
End With
Case Is = 2
Dim my3Collection As Collection, cell_3 As Range
On Error Resume Next
Set my3Collection = New Collection
With ListBox2
.Clear
For Each cell_3 In Range("F11:F6000")
If Len(cell_3) <> 0 Then
Err.Clear
my3Collection.Add cell_3.Value, cell_3.Value
If Err.Number = 0 Then .AddItem cell_3.Value
End If
Next cell_3
End With
End With ' << Edit: double End With ??? :-;
End Select
ListBox2.Font.Size = 12
ListBox2.Font.Name = "Arial"
End Sub
Section C
Listbox 2 Click This is where my code fails
Private Sub ListBox2_Click()
Dim str As String
str = ListBox2.Text
Worksheets("Report").Range("A10:AE6000").AutoFilter Field:=2, criteria:="str"
End Sub
