excel vba combobox

Question

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:

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 :(

Got any ideas?

Answer 1

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

Answer 2

The only mistake you made was not transposing and change myArray to type 'Range'

    myArray = WorksheetFunction.Transpose(Worksheets("data").Range("A4:PB4"))
     For each cell in myArray
     Me.combobox2.additem(cell)
    Next

Answer 3

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 clear method before populating the combobox seems to work fine for me. It clears then repopulates. ?

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

Answer 4

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

Answer 5

hier is the solution:

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