vba 使用excel vba根据条件填充组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19733952/
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
Populate combo box based on criteria with excel vba
提问by Ryan
PLEASE HELP! I need this done by the end of the day TODAY (11/4/13)!!! I've been trying to figure out this problem in a number of different ways all day, and I can't figure it out. Help needed!
请帮忙！我需要在今天（13 年 4 月 11 日）结束前完成这项工作！！！我一整天都在试图以多种不同的方式解决这个问题，但我无法解决。需要帮助！
I have a series of userforms that the user uses to select certain criteria that reads the data from the spreadsheet to ultimately produce a list of information in a final listbox userform. In one userform (ScoreRange), the user is asked to type in two numbers into two separate listboxes (tbScore1 and tbScore2), and then once they enter the two scores and click a command button labeled "OK", the next userform opens with a combobox (cbName), which I would like to have it populated with names from column A whose corresponding score in column E falls within the user's selected score range. I want it to do this loop search through rows 2-401. Currently, the combobox is blank when it is opened up; it is not populating correctly. Here is the current code that I have. I should mention that the ScoreRange userform is still open (it has not been unloaded yet).
我有一系列用户表单，用户使用它们来选择某些标准，这些标准从电子表格中读取数据，最终在最终的列表框用户表单中生成信息列表。在一个用户窗体 (ScoreRange) 中，要求用户在两个单独的列表框（tbScore1 和 tbScore2）中输入两个数字，然后一旦他们输入两个分数并单击标有“确定”的命令按钮，下一个用户窗体将打开并显示组合框 (cbName)，我希望用 A 列中的名称填充它，其 E 列中的相应分数落在用户选择的分数范围内。我希望它通过第 2-401 行进行循环搜索。目前，组合框在打开时是空白的；它没有正确填充。这是我拥有的当前代码。
Private Sub UserForm_Activate()
Dim i as Long
For i = 2 To 401
If Range("E" & i).Value >= ScoreRange.tbScore1.Value And Range("E" & i).Value <= ScoreRange.tbScore2.Value Then
Me.cbName.AddItem Range("A" & i).Value
End if
Next i
End Sub
回答by Ryan
Never mind, everyone. I finally decided that the only efficient way to do this was to use autofilters. Here is the final code that I used, for those who are interested.
没关系，大家。我最终决定，唯一有效的方法是使用自动过滤器。这是我使用的最终代码，供感兴趣的人使用。
Private Sub UserForm_Activate()
Dim wksheet1 As Worksheet
Set wksheet1 = Sheets("Sheet1")
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Dim cbRange As Range
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=5, Criteria1:= _
">=" & tbScore1.Value, Operator:=xlAnd, Criteria2:="<=" & tbScore2.ValueActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=5, Criteria1:= _
">=" & tbScore1.Value, Operator:=xlAnd, Criteria2:="<=" & tbScore2.Value
For Each cbRange In Range("A2:A" & LR).SpecialCells(xlCellTypeVisible)
Me.cbName.AddItem cbRange.Value
Next cbRange
ActiveSheet.AutoFilterMode = False
ActiveSheet.ShowAllData
End Sub
