Adding second column to a dynamic rowsource in Excel Userform

Question

I'm trying to populate a userform with a second column to make it more user-friendly. Currently it shows an ID number, but I also want it to display a name.

I found an answer in this thread (adding two columns to vba userform combobox) which seems to do what I want, but I can't quite get the code to match up with mine as I'm using a dynamic reference to populate the combobox, rather than static references.

Here's my attempt at the initialize macro:

Dim C As Range
Sheets("Consent_Outcome").Activate
Range("A2", Range("A" & Rows.Count).End(xlUp)).Name = "Dynamic"
Range("L2", Range("L" & Rows.Count).End(xlUp)).Name = "Dynamic2"
Me.cboURN.RowSource = "Dynamic"

For Each C In cboURN
Me.cboURN.Offset(1, 0) = "Dynamic2"

I want it to populate the second column in the combobox with the value from column L, which apparently I should do by setting the 2nd column value as ComboBox1.Column(1,{rowIndex}) = 'the value'`, but I can't get it working with the Dynamic reference. Can anyone suggest how to do this?

Answer 1

Since you've hard-coded "A2" as the top of the range, I gather you are assuming that the data will always start on the second row. If you assign the range L2 to a range variable, you can use an incremental loop to assign the range variable's value to the appropriate list value, then .Offset(1,0) and reassign the range variable to increment down the column.

Note: My code should work with an excel combobox; I don't know if it'd work for an Access combobox.

Assuming that cboURN is your combobox:

Dim C As Range
Dim index as Integer

Sheets("Consent_Outcome").Activate
Range("A2", Range("A" & Rows.Count).End(xlUp)).Name = "Dynamic"
' Unnecessary: Range("L2", Range("L" & Rows.Count).End(xlUp)).Name = "Dynamic2"
Me.cboURN.RowSource = "Dynamic"

Set C = Range("L2")

With cboURN 'Or Me.cboURN; you can reference named controls without the preceding "Me."
    For index = 0 to .ListCount - 1
        .List(index, 1) = C 'Or C.Value2
        Set C = C.Offset(1,0)
    Next index
End With

Alternatively, you should be able to also use this code to populate the combobox (although if you need the items added in a specific order, this option may not be the best):

Dim C As Range
Dim cell As Range
Dim index as Integer

Sheets("Consent_Outcome").Activate
Set C = Range("A2", Range("A" & Rows.Count).End(xlUp))

With cboURN 'Or Me.cboURN; you can reference named controls without the preceding "Me."
    For each cell in C 
        .AddItem cell
        .List(.ListCount - 1, 1) = cell.Offset(0, 11) 'Offset 11 columns to column L
    Next cell
End With