首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >利用工作表中的控件(ComboBox)控制自动筛选

利用工作表中的控件(ComboBox)控制自动筛选
EN

Stack Overflow用户
提问于 2019-05-16 21:06:28
回答 3查看 132关注 0票数 0

将筛选的数据复制到用户通过ComboBox (用户表单或ActiveX)输入筛选条件的另一个工作表

最终,我希望将选择的数据从一个工作表上的主数据集中复制到另一个工作表。为了隔离要复制的相关数据,我计划使用过滤器。我想在工作表中包含一个下拉列表对象,允许用户选择筛选条件

这本质上只是复制Excel数据筛选函数,而不是在位置上限制到要筛选的列。

我假设用户表单或ActiveX ComboBox是实现这一点的方法?这就是我失败的地方。我不知道如何调用用户从ComboBox中选择的值来通知AutoFilter过程。

代码语言:javascript
复制
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
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2019-05-22 18:18:05

我最终将操作拆分为两个模块: M_00_Filter()和M_00_Generate()。

第一种方法根据从用户窗体组合框中选择的内容来筛选数据,该组合框静态地分配了同一工作簿中另一个工作表的范围。

代码语言:javascript
复制
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

第二个模块复制可见数据并将其复制到另一个工作簿。再次感谢您的帮助。

感谢大家抽出时间来。干杯。

票数 0
EN

Stack Overflow用户

发布于 2019-05-16 21:44:19

基本上,自动筛选所做的是隐藏与所选内容不匹配的整行。在本例中,如果在B列中,我将假定您正在查找的信息

代码语言:javascript
复制
'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

代码语言:javascript
复制
for i = 2 to lr
activesheet.range("B"&i).hidden = false
next i

希望它已经解决了你的问题,我还没有测试你,但是我的想法是你得到了逻辑

票数 0
EN

Stack Overflow用户

发布于 2019-05-16 23:02:46

所以我不确定我是否100%的做到了你想要做的,但是这里有一个方法:

以下方法适用于ActiveX组合框。

为了简单起见,我们假设您有以下设置:

筛选器值在A1:A8中,您希望按日期名称筛选列C。

首先,您需要一个用于初始化combobox的sub。这可以在你的模块中找到。如何调用此sub取决于您。如果这个列表需要定期更新,你可以有一个专门的按钮。

代码语言:javascript
复制
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所属的工作表中。

代码语言:javascript
复制
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,也可以使用事件本身做任何您想做的事情。我在这里应用了第一种方法,但它实际上是您的选择。

代码语言:javascript
复制
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

结果是这样的:

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56169348

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档