使用 VBA，如何根据 excel 表中的特定 comlumn 填充 ComboBox 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7718347/
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
Using VBA, how to populate ComboBox values based on specific comlumn in an excel table
提问by Gill
Using VBA, I'm trying to build this sophisticated form to add new rows to an existing table. I want to have this ComboBox that will list the values already exists in the one of the table's column.
使用 VBA，我试图构建这个复杂的表单来向现有表添加新行。我想要这个 ComboBox 将列出表列之一中已经存在的值。
I'm new to VBA. and I tried some Range/Selection and sort combinations with no lack so far...
我是 VBA 的新手。到目前为止，我尝试了一些范围/选择和排序组合，但没有任何不足......
回答by TheFuzzyGiggler
Here's what you're looking for.. It should get you started, Just adapt the Sheets and Range to your needs.
这就是您要查找的内容。它应该可以帮助您入门，只需根据您的需要调整工作表和范围即可。
Dim cmb as ComboBox
Dim rng as Range
Set cmb = Worksheets("Sheet1").ComboBox1
'To fill based on range
For Each rng in Worksheets("Sheet2").Range("C2:C300")
Cmb.AddItem Rng.Value
Next
'To fill from table where ListColumns(N) is the specific column
Set rng = Sheet2.ListObject(1).ListColumns(3).Range
For Each rng in rng
Cmb.AddItem Rng.Value
Next
Cmb.ListIndex = 0
*EDITED:*Chris is right, my original code had errors. Posted answer on way to work didn't have time to check. The code above works fine. Chris suggestion on just using .value to fill is quicker. I honestly didn't know you could do it like that.
*编辑：*Chris 是对的，我的原始代码有错误。在上班路上发布的答案没有时间检查。上面的代码工作正常。Chris 建议只使用 .value 来填充更快。老实说，我不知道你可以那样做。
回答by uttam patel
you need to create your table column range,
Either you can insert your row in side the range
Or you need to first add row to table and resize your range
and pass that range pass to SetRng parameter,
userFormName is user form name,
ControlName is combobox name
您需要创建表格列范围，
您可以在范围内插入行
或者您需要首先向表格添加行并调整范围
并将该范围传递给 SetRng 参数，
userFormName 是用户表单名称，
ControlName 是组合框姓名
Public Function FillRangeComboBox(userFormName As String, ControlName As String, SetRng As Range) As Boolean
Dim ObjFormName As Object: Set ObjFormName = ThisWorkbook.VBProject.VBComponents(userFormName)
Dim ObjControlName As MSForms.ComboBox: Set ObjControlName = ObjFormName.Designer.Controls(ControlName)
''set combobox value
With ObjControlName
.RowSource = SetRng.Address
End With
End Function
