JimS
04-29-2010, 06:27 PM
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
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
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
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