选择多个列表框项以设置过滤器 [英] Selecting multiple listbox items to set a filter
问题描述
我正在尝试选择业务的年份和类型(1个或更多),然后自动过滤列.这样一来,我只能使用1个宏,而不用为所有其他选择都做很多.
I'm trying to select a YEAR and TYPE of business (1 or more) and then autofilter a column. That way I can use only 1 macro instead of making many for all the alternatives.
这是我目前所拥有的:
Private Sub Botton1_Click()
Public Platform As String
Public Year as Integer
Platform = UserForm1.LB2.Text
Year = UserForm1.LB1.value
Unload UserForm1
End Sub
......
Private Sub UserForm_Initialize()
With LB1
.AddItem "2016"
.AddItem "2017"
.AddItem "2018"
End With
With LB2
.AddItem "CMP"
.AddItem "AS"
.AddItem "MasterBread"
.AddItem "CMI -Andino"
.AddItem "CMI -Brasil"
.AddItem "CMI -CAMEC"
.AddItem "CMI -ConoSur"
.AddItem "Global"
End With
End Sub
年份将始终仅是1个值,但业务类型可以是1个或更多.
The year will always be only 1 value but business type can be 1 or more.
如何存储列表框的多个值以将变量称为过滤器?
How do I store multiple values of the listbox in order to call the variable as a filter?
这是我需要调用的变量:
This is were I need to call the variables:
ActiveSheet.Range("$A$1:$G$1500").AutoFilter Field:=4, Criteria1:="2016"
ActiveSheet.Range("$A$1:$G$1500").AutoFilter Field:=2, Criteria1:="=AS", _
Operator:=xlOr, Criteria2:="=MASTER BREAD"
在此示例中,我需要过滤"2016"和"AS& MASTER BREAD".
In this example I needed to filter "2016" and "AS & MASTER BREAD".
推荐答案
基于您在问题中提供的内容,按钮应使用的注释代码:
Commented code that your button should use, based on what you've provided in your question:
Private Sub Botton1_Click()
Dim rData As Range
Dim sFilters As String
Dim i As Long
Set rData = ActiveSheet.Range("$A$1:$G$1500")
rData.AutoFilter 'Remove any existing filters
'Get the selected year
If LB1.ListIndex > -1 Then
'Filter the year
rData.AutoFilter 4, LB1.Text
End If
'Gather all selected values in LB2 (type of business)
For i = 0 To LB2.ListCount - 1
'If value is selected, add to sFilters variable
If LB2.Selected(i) Then sFilters = sFilters & "|" & LB2.List(i)
Next i
If Len(sFilters) > 0 Then
'Filter on selected values
rData.AutoFilter 2, Split(sFilters, "|"), xlFilterValues
End If
End Sub
这篇关于选择多个列表框项以设置过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!