PDA

View Full Version : Fill a combo Box from a range



Djblois
12-26-2007, 02:40 PM
I know how to fill a combobox from a a range setup in a column but I want to fill it from a row.(Row 1) I have tried many ways to do but I can't. here is how I tried:

aColumns = Application.WorksheetFunction.Transpose(Range(Cells(1, 1), Cells(1, FinalColumn(ActiveSheet))))

Me.comCol1.List = aColumns

That is the last way I tried, but I tried many similar ways. Like RowSource, an Array without transpose. I can't figure it out. Please help. It is probably something so simple, just can't figure it out.

lucas
12-26-2007, 02:56 PM
Daniel,
I would transpose it somewhere on a worksheet and use rowsource....that's why it's called rowsource. Just a suggestion.

Djblois
12-26-2007, 03:01 PM
Lucas,

Thank you for the suggestion. If there is no direct way to do it then I will do it that way. Actually, I have one more thing I would like to do with it, If the cell is blank for any of them, I want it to list the column Letter. So maybe a loop will work. looping through each cell and filling one at a time but if if is blank fill it with "Column" and the column letter. Is this possible? If not I will use your way lucas.

Daniel

lucas
12-26-2007, 03:14 PM
Or you could make an array out of it and transpose the array:
Option Explicit
Private Sub UserForm_Initialize()
Dim myarray As Variant
Dim i
myarray = Range("a1:e1").Value
For i = 1 To UBound(myarray)
Next
Me.ComboBox1.List = Application.Transpose(myarray)
End Sub

rory
12-27-2007, 06:45 AM
You could use:
Dim rngCell As Range
For Each rngCell In Range(Cells(1, 1), Cells(1, FinalColumn(ActiveSheet)))
If Len(rngCell.Value) > 0 Then
comCol1.AddItem rngCell.Value
Else
comCol1.AddItem Replace(Cells(1, rngCell.Column).Address(False, False), "1", "")
End If
Next rngCell
End Sub