Combo Box help. Struggling to get range in to it.

[BigShango]

Hi,

I have a combo box in a user form. I need to populate it with a list of names from my spreadsheet. The names are listed broken in to first and last name. I need them joined together in the combo box and the same on the output from it.

I really can't get my head around arrays at all. Is there a simple way for me to do this?

I'm fine with adding the names together, I just can't figure out how to put them into the combo box

I need something like

For Row = 2 to LastRow
    FirstName = Range("A" & Row)
    LastName = Range("B" & Row)
    FullName = FirstName & " " & LastName
    *FullName in to combo box list*
Next Row

I don't want to list the full names on the spreadsheet anywhere if possible. The purpose of it is that I have a whole series of individual sheets in the workbook named with the full names, I intend to use the combo box to go to the selected sheet.

I hope this makes sense. Any ideas?

Thanks

 

[Jerry Sullivan]

Hi BigShango,

Here's two examples. The first uses the ComboBox.AddItem Method, the second uses ComboBox.List Property.

Either could be placed in your UserForm's Code module then called by UserForm_Initialize...

Sub PopulateComboBox()
 Dim lLastRow As Long, lRow As Long
 Dim sFullName As String
  
 '--clear any existing names
 Me.cboFullNames.Clear
 
 With Sheets("Sheet1")
   lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   For lRow = 2 To lLastRow
      sFullName = .Range("A" & lRow) & " " & Range("B" & lRow)
      
      Me.cboFullNames.AddItem (sFullName)
   Next lRow
 End With
End Sub

Sub PopulateComboBox2()
 Dim lLastRow As Long
 Dim vFullNames As Variant
 
 '--clear any existing names
 Me.cboFullNames.Clear
 
 With Sheets("Sheet1")
   lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   vFullNames = Evaluate( _
      .Range("A2:A" & lLastRow).Address(External:=True) _
      & "& "" "" &" & _
      .Range("B2:B" & lLastRow).Address(External:=True))
   Me.cboFullNames.List = vFullNames
 End With

End Sub

Private Sub UserForm_Initialize()
 Call PopulateComboBox2
End Sub

Modify the WorkSheet Name and ComboBox Name to match yours.

 

[BigShango]

Thanks Jerry, I'll give that a try. Also thanks for the shorter way of finding the last row!

Edit - First example works perfectly.