-
UserFrom ListBox FilterInPlace on inactive sheet
The following code filters Sheet3 using the items that the user selects using a MultiSelect ListBox.
I’m trying to convert this code so that it will do the FilterInPlace without activating Sheet3.
Does anyone know if this is possible?
Thanks for any help.
JimS
[vba]
Private Sub OKButton1_Click()
If Me.ListBox1.ListIndex = -1 Then
MsgBox "You must select a District before proceeding!"
Exit Sub
End If
Dim SelectedItemArr
Dim SelectedItemStr, AdvFilterStr As String
Dim ItemCnt, SelItemCnt As Integer
For ItemCnt = 0 To DistrictSelector.ListBox1.ListCount - 1
If DistrictSelector.ListBox1.Selected(ItemCnt) Then
SelectedItemStr = SelectedItemStr & DistrictSelector.ListBox1.List(ItemCnt) & ","
End If
Next
If Len(SelectedItemStr) > 0 Then
SelectedItemStr = Mid(SelectedItemStr, 1, Len(SelectedItemStr) - 1)
SelectedItemArr = Split(SelectedItemStr, ",")
Sheet3.Cells(1, 71).Value = "* DMR Field | District" ' Needed for the advanced filter
For ItemCnt = 0 To UBound(SelectedItemArr)
Sheet3.Cells(ItemCnt + 2, 71).Value = SelectedItemArr(ItemCnt)
SelItemCnt = SelItemCnt + 1
Next
AdvFilterStr = "BS1:BS" & SelItemCnt + 1
Sheet3.Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(AdvFilterStr), Unique:=False
End If
If ItemCnt > 1 Then
Dim i, TmpList As String
TmpList = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
TmpList = TmpList & " " & ListBox1.List(i)
End If
Next i
End If
[/vba]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules