利用工作表中的控件(ComboBox)控制自动筛选
将筛选的数据复制到用户通过ComboBox (用户表单或ActiveX)输入筛选条件的另一个工作表
最终，我希望将选择的数据从一个工作表上的主数据集中复制到另一个工作表。为了隔离要复制的相关数据，我计划使用过滤器。我想在工作表中包含一个下拉列表对象，允许用户选择筛选条件
这本质上只是复制Excel数据筛选函数，而不是在位置上限制到要筛选的列。
我假设用户表单或ActiveX ComboBox是实现这一点的方法？这就是我失败的地方。我不知道如何调用用户从ComboBox中选择的值来通知AutoFilter过程。
Sub M_00()
Dim wB As Workbook
Dim wS1, wS2 As Worksheet
'Dim x As ???
Dim rng3 As Range
Dim lrowS3 As Long
Set wB = This.Workbook
Set wS1 = wB.Sheets(1)
Set wS3 = wB.Sheets(3)
'define range for Combo Box drop down list from data on Sheet 3
lrowS3 = wS3.Cells(Rows.Count, 1).End(xlUp).Row
Set rng3 = wS3.Range(Cells(2, 2), Cells(lrowS3, 2))
'Don't know how to
' a) assign my rng3 values to the ComboBox drop down list
' b) call the selection from the ComboBox to inform my AutoFilter
wS1.ComboBox1.List = rng3.Value
x = wS1.ComboBox1.Selection
' turn off any autofilters that may already be active in Sheet 1
wS1.AutoFilterMode = False
' filter column 4 based on combo box selection
wS1.UsedRange.AutoFilter Field:=4, Criteria1:=x
'Once filtered I plan you assign the visible data to an array and copy
'it to the required sheet.
End Sub回答 3
Stack Overflow用户
发布于 2019-05-22 18:18:05
我最终将操作拆分为两个模块: M_00_Filter()和M_00_Generate()。
第一种方法根据从用户窗体组合框中选择的内容来筛选数据，该组合框静态地分配了同一工作簿中另一个工作表的范围。
Sub M_00_Filter()
Dim wB As Workbook
Dim myindex As Long
Dim filterValue As String
Dim wS As Worksheet
Dim rng2BF As Range
Set wB = ThisWorkbook
Set wS = wB.Sheets("Master Schedule")
'~~> Currently selected item index at runtime
myindex = wS.Shapes("Drop Down 1").ControlFormat.Value
'~~> Currently selected item value at runtime
filterValue = wS.Shapes("Drop Down 1").ControlFormat.List(myindex)
lRow = wS.Cells(wS.Rows.Count, 4).End(xlUp).Row
lCol = wS.Cells(5, wS.Columns.Count).End(xlToLeft).Column
'Set rngToBeFiltered
Set rng2BF = wS.Range(Cells(5, 1), Cells(lRow, lCol))
If filterValue = "Select a Phase" Then
rng2BF.AutoFilter field:=4 'If No Filter is selected then all data is displayed
Else
rng2BF.AutoFilter field:=4, Criteria1:=filterValue 'else the filter is applied
End If
End Sub第二个模块复制可见数据并将其复制到另一个工作簿。再次感谢您的帮助。
感谢大家抽出时间来。干杯。
Stack Overflow用户
发布于 2019-05-16 21:44:19
基本上，自动筛选所做的是隐藏与所选内容不匹配的整行。在本例中，如果在B列中，我将假定您正在查找的信息
'getting the value I am looking for
cbVal = comboBox1.Text
'getting the last row filled
lr = activesheet.cells(rows.count,2).end(xlup).row
'asuming that B1 is the title of the table, so I'm starting since B2 to last row
for i = 2 to lr
if not activesheet.range("B"&i) == cbVal then
activesheet.range("B"&i).entireRow.Hidden = true
end if
next i最后，您设置了一个用于隐藏行的sub
for i = 2 to lr
activesheet.range("B"&i).hidden = false
next i希望它已经解决了你的问题，我还没有测试你，但是我的想法是你得到了逻辑
Stack Overflow用户
发布于 2019-05-16 23:02:46
所以我不确定我是否100%的做到了你想要做的，但是这里有一个方法：
以下方法适用于ActiveX组合框。
为了简单起见，我们假设您有以下设置：
筛选器值在A1:A8中，您希望按日期名称筛选列C。
首先，您需要一个用于初始化combobox的sub。这可以在你的模块中找到。如何调用此sub取决于您。如果这个列表需要定期更新，你可以有一个专门的按钮。
Sub initializeComboBox() 'The name of the Sub is self explanatory
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Name of your Worksheet") 'The worksheet where the combobox is located
With sht.OLEObjects("ComboBox1").Object 'Referring to the combobox from outside the worksheet where it's located can be a bit tricky
.Clear 'Clear the combobox's list
.List = sht.Range("A1:A8").Value 'assign the values contained in range A1:A8 to the combobox's list
End With
End Sub然后，您需要一个事件，该事件将在用户选择组合框时捕获组合框的值的更改。它需要位于combobox所属的工作表中。
Private Sub ComboBox1_Change() 'This event fires up whenever the user selects an item from the dropdown menu
Dim x As String
x = ComboBox1.Value 'referring to the combobox from inside the worksheet where it belongs is easier
Module1.testFilter (x)
End Sub然后，您可以拥有一个位于模块中并由该事件调用的sub，也可以使用事件本身做任何您想做的事情。我在这里应用了第一种方法，但它实际上是您的选择。
Sub testFilter(filterValue As String) 'A sub that is located in the module and applies the filter. This is called whenever the Value of the combobox is changed
Dim sht As Worksheet
Dim rngToBeFiltered As Range
Set sht = ThisWorkbook.Worksheets("Name of your Worksheet") 'The worksheet where the range that needs to be filtered is located
Set rngToBeFiltered = sht.Columns("C")
If filterValue = "No Filter" Then
rngToBeFiltered.AutoFilter field:=1 'If No Filter is selected then all data is displayed
Else
rngToBeFiltered.AutoFilter field:=1, Criteria1:=filterValue 'else the filter is applied
End If
End Sub结果是这样的：
https://stackoverflow.com/questions/56169348
复制相似问题
腾讯云开发者
