Solved: Dynamically populate combobox in userform

[edjohns]

Hello again friends,

I am trying to populate a combobox (inside a userform) with 2 columns. Like:
COL 1 COL 2
A 1
B 2
C 3
... and so on.

I am using the list method (although i have tried with additem to no success either). What I need is for the box to display a list like:
A : 1
B : 2
C : 3
... and so on (with the colom and all, although not as important. But ideally, I need to have a separator between each column item in each row)

Here is the code I've have so far:
[vba]

Sub UserForm_Initialize()
Dim sht As Worksheet 'source worksheet
Dim nRow As Double '# of items in range
Dim lst As Range 'Range where 2 source coulumns are (cols 6 and 7)

Set sht = ThisWorkbook.Sheets("VALUES")
nRow = sht.Range("F2").End(xlUp).row
Set lst = sht.Range(Cells(2, 6), Cells(nRow, 7))

With frm_incidencia.cbb_defectos
.ColumnCount = 2
.BoundColumn = 1 'return value from column 1 when item selected
.list = lst.Value
End With

End Sub

[/vba] I'm getting strange behaviors, like the box being populated from rows 1 and 2 of the spreadsheet only from the first column.
I am confused.

As always, thank you

[jolivanes]

Should that be 3 columns? 1 for the A, one for the : and one for the 1 etc

[jolivanes]

If you don't really need multiple columns, try this

[VBA]Private Sub UserForm_Initialize()
Dim i As Single
Dim j As Single
Dim c As Range
ComboBox1.Clear 'Make sure the Listbox is empty
i = 1
j = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
ComboBox1.AddItem Cells(i, 1).Value & " : " & Cells(i, 2).Value
i = i + 1
j = j + 1
Next
End Sub[/VBA]

[jolivanes]

For multiple columns, you could try this

[vba]
Private Sub UserForm_Initialize()
Dim i As Single
Dim j As Single
Dim c As Range
ComboBox1.Clear 'Make sure the Listbox is empty
ComboBox1.ColumnCount = 2 'Set the column Amount
i = 1
j = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
ComboBox1.AddItem Cells(i, 1).Value & " : "
ComboBox1.List(j, 1) = Cells(i, 2).Value
i = i + 1
j = j + 1
Next
End Sub
[/vba]

[mikerickson]

If you adjust the range, perhaps
[VBA]Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = ";15;"
With Sheet1.Range("A1:B10")
.Columns(2).Insert shift:=xlToRight
.Columns(2).Value = ":"
ListBox1.List = .Value
Columns(2).Delete shift:=xlToLeft
End With
End Sub[/VBA]

[edjohns]

If you don't really need multiple columns, try this

[VBA]Private Sub UserForm_Initialize()
Dim i As Single
Dim j As Single
Dim c As Range
ComboBox1.Clear 'Make sure the Listbox is empty
i = 1
j = 0
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
ComboBox1.AddItem Cells(i, 1).Value & " : " & Cells(i, 2).Value
i = i + 1
j = j + 1
Next
End Sub[/VBA]

first of all, thank you. this is not working how its supposed to. I'm getting a strange list in the combo box. it goes from B1 to B6. the range i need to display is from F2 to the last row on F column where i have a value; and the same for the second item in column G2. for example (F2:G20) or last row instead of the 20.
here is the code, exactly as i typed it in:
[VBA]
Sub UserForm_Initialize()

Dim c As Range
Dim i As Single
Dim j As Single

frm_incidencia.cbb_defectos.Clear
frm_incidencia.cbb_defectos.ColumnCount = 2

i = 1
j = 0

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
frm_incidencia.cbb_defectos.AddItem Cells(i, 1).Value & " : "
frm_incidencia.cbb_defectos.list(j, 1) = Cells(i, 2).Value
i = i + 1
j = j + 1
Next

End Sub
[/VBA]

also, i need to return the value from the first column of the list; in this case F. i think i need to set boundcolumn to 1 if i'm not mistaken. i've also tried using F instead of A in the for each line to no success, i also get the values from column B to a different row than when using A liske the code above.

i referenced the combobox after the form, because i plan to move the code to another module and just call that routine from inside the form.

again, i can't thank u guys enough for helping me out

[Kenneth Hobs]

[VBA]Private Sub CommandButton1_Click()
MsgBox "Col0=" & ComboBox1.List(ComboBox1.ListIndex, 0) & vbLf & "Col2=" & _
ComboBox1.List(ComboBox1.ListIndex, 2), vbInformation
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim f As Long, g As Long, r As Long, ws As Worksheet
Dim i As Long, j As Long, a() As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")
f = ws.Range("F" & Rows.Count).End(xlUp).Row
g = ws.Range("G" & Rows.Count).End(xlUp).Row
r = f
If g > f Then r = g

ReDim a(1 To r - 1, 1 To 3)
For i = 2 To r
j = i - 1
a(j, 1) = ws.Range("F" & i).Value
a(j, 2) = ":"
a(j, 3) = ws.Range("G" & i).Value
Next i

ComboBox1.ColumnCount = 3
ComboBox1.List = a()
End Sub[/VBA]

[edjohns]

@Kenneth Hobs
thank you.

i'm only getting the " : " and G column displayed, not the F column though.
thanks again

[Kenneth Hobs]

Works for me. Make sure that column F has data. You can try my attachment.

[snb]

if you want the F1:G20 in a 2-column combobox:
[vba]
Sub columns2_snb()
ComboBox1.List = Sheets("sheet1").Cells(1, 5).CurrentRegion.Value
ComboBox1.ColumnCount = UBound(ComboBox1.List, 2) + 1
End Sub
[/vba]
If you want the two columns integrated/concatenated into 1 column:

[vba]Sub columns1_snb()
ComboBox1.List = [sheet1!F1:F30&":"&sheet1!G1:G30]
End Sub[/vba]

If currentregion doesn't apply because of empty rows:

[vba]
Sub columns2_snb()
ComboBox1.List = range(Sheets("sheet1").Cells(1, 5),sheets("sheet1).cells(rows.count,5).end(xlup).offset(,1)).Value
ComboBox1.ColumnCount = UBound(ComboBox1.List, 2) + 1
End Sub
[/vba]

[edjohns]

hey guys,

i ended up concatenating the 2 columns into one inside the spreadsheet, and populating the combobox with the range object from that column. since i need to retrieve the value from the 1st column, i will use a vlookup to get the corresponding value from the same row.

i would like to thank all of u guys for taking the time and effort to help me out. lets hope one day i can return the favor (maybe after a million hours of coding haha). really, thank u.