excel vba 组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13115034/
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 combobox
提问by David Pesetsky
I want to populate a combobox with contents from a row (not a column) of values from another sheet, using VBA. If I assign the List to the row's range I only see the 1st value, so I guess excel insists on having a column of ranges. So, I was trying something like adding items:
我想使用 VBA 用另一张工作表中一行（而不是一列）值的内容填充组合框。如果我将 List 分配给行的范围，我只会看到第一个值，所以我猜 excel 坚持有一列范围。所以，我正在尝试添加项目之类的东西：
Private Sub ComboBox2_GotFocus()
Dim i As Integer
Dim myArray As Variant
myArray = Worksheets("data").Range("A4:PB4").Value
For i = LBound(myArray) To UBound(myArray)
Me.ComboBox2.AddItem myArray(i)
Next
End Sub
Two problems. I don't think that's an array, it's a range. And, if I use add, I need to clear it everytime, but somehow using ComboBox2.Clear in the same routine clears it even after it's loaded? So nothing shows up :(
两个问题。我不认为那是一个数组，它是一个范围。而且，如果我使用添加，我需要每次都清除它，但不知何故在同一例程中使用 ComboBox2.Clear 即使在加载后也会清除它？所以什么都没有显示:(
Got any ideas?
有什么想法吗？
回答by David Pesetsky
This seemed to do the trick:
这似乎可以解决问题：
Private Sub ComboBox2_GotFocus()
myArray = WorksheetFunction.Transpose(Worksheets("data").Range("A4:PB4"))
With Me.ComboBox2
.List = myArray
End With
End Sub
回答by David Pesetsky
A better solution:
更好的解决方案：
Private Sub ComboBox1_GotFocus()
Dim myArray As Variant
lastcol = Worksheets("data").Range("A4").End(xlToRight).Column
With Worksheets("data")
Set SourceRng = .Range(.Cells(4, 1), .Cells(4, lastcol))
End With
myArray = WorksheetFunction.Transpose(SourceRng)
With Me.ComboBox1
.List = myArray
End With
End Sub
回答by Suliman Farzat
hier is the solution:
hier 是解决方案：
Sub PopulateComboBox()
Dim n As Integer, i, names() As String
n = WorksheetFunction.CountA(Rows("1:1"))
ReDim names(n) As String
For i = 1 To n
names(i) = Cells(1, i)
NameForm.ComboBox1.AddItem names(i)
Next i
End Sub
回答by David Venegoni
The only mistake you made was not transposing and change myArray to type 'Range'
您犯的唯一错误是没有转置并将 myArray 更改为类型“Range”
myArray = WorksheetFunction.Transpose(Worksheets("data").Range("A4:PB4"))
For each cell in myArray
Me.combobox2.additem(cell)
Next
回答by Brad
You probably want to avoid the array declaration here and just use the native format (range) of the thing you're using. Also calling the clearmethod before populating the combobox seems to work fine for me. It clears then repopulates. ?
您可能希望避免此处的数组声明，而只使用您正在使用的事物的本机格式（范围）。clear在填充组合框之前调用该方法似乎对我来说工作正常。它清除然后重新填充。?
Private Sub Worksheet_Activate()
Dim i As Integer
Dim myRange As Range
ComboBox1.Clear
Set myRange = Worksheets("data").Range("A4:PB4")
Dim c As Range
For Each c In myRange
Me.ComboBox1.AddItem c.Value
Next
End Sub
