vba 应用自动筛选并在用户窗体列表框中显示结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18624550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apply AutoFilter and display results in UserForm ListBox?
提问by BrianLender
Right now I have a UserForm that looks like this:
现在我有一个看起来像这样的用户窗体：
I have a spreadsheet that looks like this:
我有一个看起来像这样的电子表格：
I am using the following code in the UserForm_Initialize event to apply an AutoFilter to my data. I need to display the results of the AutoFilter in my listbox which is named "boxPolicyList".
我在 UserForm_Initialize 事件中使用以下代码将自动过滤器应用于我的数据。我需要在名为“boxPolicyList”的列表框中显示自动筛选的结果。
Worksheets("defaults").Select
Me.boxDateBegin.Value = ActiveSheet.Range("E4").Value
Me.boxDateEnd.Value = ActiveSheet.Range("F4").Value
Workbooks.Open Filename:="Z:\Stuff\production\production_database.xlsm"
Worksheets("policies").Select
With ActiveSheet
.AutoFilterMode = False
With .Range("A1:F1")
.AutoFilter
.AutoFilter field:=1, Criteria1:=">=" & Me.boxDateBegin.Value, _
Operator:=xlAnd, Criteria2:="<=" & Me.boxDateEnd.Value
.AutoFilter field:=3, Criteria1:="Bear River Mutual"
End With
End With
Me.txtTotalPolicies.Caption = ActiveSheet.Range("J1").Value
Me.txtTotalPremium.Caption = ActiveSheet.Range("H1").Value
Me.txtTotalPremium.Caption = Format(Me.txtTotalPremium.Caption, "$#,###,###.00")
Workbooks("production_database.xlsm").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Does anyone know how this can be done?
有谁知道如何做到这一点？
采纳答案by tigeravatar
Perhaps...
也许...
Private Sub UserForm_Initialize()
Dim rngVis As Range
Me.boxDateBegin.Value = Sheets("defaults").Range("E4").Value
Me.boxDateEnd.Value = Sheets("defaults").Range("F4").Value
With Workbooks.Open("Z:\Stuff\production\production_database.xlsm")
With Sheets("policies")
.AutoFilterMode = False
Me.txtTotalPolicies.Caption = .Range("J1").Value
Me.txtTotalPremium.Caption = Format(.Range("H1").Value, "$#,###,###.00")
With Intersect(.UsedRange, .Range("A:F"))
.Sort Intersect(.Cells, .Parent.Columns("C")), xlAscending, Intersect(.Cells, .Parent.Columns("A")), , xlAscending, Header:=xlGuess
.AutoFilter 3, "Bear RIver Mutual"
.AutoFilter 1, ">=" & Me.boxDateBegin.Value, xlAnd, "<=" & Me.boxDateEnd.Value
On Error Resume Next
Set rngVis = .Offset(1).Resize(.Rows.Count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rngVis Is Nothing Then Me.boxPolicyList.List = rngVis.Value
End With
End With
.Close False
End With
End Sub
