Excel VBA - 在选择另一个组合框的情况下填充组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28215922/
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
Excel VBA - Populate a Combobox given selection of another Combobox
提问by Tyler
I am trying to populate a second combobox given the selection of a first combobox. The first combobox is the name of all the columns on the sheet. The second combobox should display all of the values in that column except for duplicates. The code below populates combobox1. I am struggling with how to take data out of a column given the varying column name. Thank you for any help.
鉴于选择了第一个组合框，我正在尝试填充第二个组合框。第一个组合框是工作表上所有列的名称。第二个组合框应显示该列中除重复项外的所有值。下面的代码填充了combobox1。我正在努力解决如何在给定不同列名的情况下从列中取出数据。感谢您的任何帮助。
Dim myArray As Variant
lastcol = Sheets(4).Range("A1").End(xlToRight).Column
With Sheets(4)
Set SourceRng = .Range(.Cells(1, 1), .Cells(1, lastcol))
End With
myArray = WorksheetFunction.Transpose(SourceRng)
With Me.ComboBox1
.List = myArray
End With
采纳答案by jDave1984
You could try to get the listindexof combobox1. Keep in mind that the ListIndex is 0-Based while Excel Rows and Columns are not:
你可以尝试获得listindex的combobox1。请记住，ListIndex 是基于 0 的，而 Excel 行和列不是：
Private Sub ComboBox1_AfterUpdate()
Dim selectedCol as Variant
selectedCol = Me.ComboBox1.ListIndex + 1
Set SourceRng = ws.Range(Cells(2, selectedCol), Cells(4, selectedCol))
Me.ComboBox2.List = WorksheetFunction.Transpose(SourceRng)
End Sub
To get rid of the duplicate values and junk: Set SourceRng = ws.Range(Cells(varRow, Me.ComboBox1.ListIndex + 1), Cells(varRow2, Me.ComboBox1.ListIndex + 1)).RemoveDuplicates Columns:= selectedCol, Header:=xlNo
摆脱重复值和垃圾： Set SourceRng = ws.Range(Cells(varRow, Me.ComboBox1.ListIndex + 1), Cells(varRow2, Me.ComboBox1.ListIndex + 1)).RemoveDuplicates Columns:= selectedCol, Header:=xlNo
Here is a workaround for removing the duplicates. Using the RemoveDuplicates function of the Range class will delete your rows, and I'm assuming you don't want that:
这是删除重复项的解决方法。使用 Range 类的 RemoveDuplicates 函数将删除您的行，我假设您不希望这样：
Private Sub ComboBox1_AfterUpdate()
Dim colSelect As Integer
colSelect = Me.ComboBox1.ListIndex + 1
Set SourceRng = ws.Range(Cells(2, colSelect), Cells(5, colSelect))
SourceRng.Sort SourceRng, xlDescending
Dim c
For Each c In SourceRng.Cells
If c.Value c.Offset(-1, 0).Value Then
Me.ComboBox2.AddItem c.Value
End If
Next
'You would take out the array and assigning it to the Combobox2.List
End Sub
