PDA

View Full Version : UserFrom ListBox FilterInPlace on inactive sheet



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