Populating a ComboBox from VBA with 2 columns

Discussion in 'Microsoft Excel Misc' started by df, Jul 8, 2003.

  1. df

    df Guest

    Hi I need to populate an excel combo box with recordset
    fields i already retrieve from an SQL Database. I need to
    columnes code and description. For example: Employee ID
    and Employee Lastname. Y need to show the Lastname in the
    combo and retrive the selected Employee ID from it.

    I tried
    ComboBox.AddItem()

    But I could only add an item with one field. How can I add
    the couple Employee ID - LastName? (I discard the
    concatenation method).

    Regards
    Daniel
     
    df, Jul 8, 2003
    #1
    1. Advertisements

  2. If those values are on a worksheet in adjacent columns, you could pick up that
    range and dump it into an array. Then use the array to fill the combobox:

    Option Explicit
    Private Sub UserForm_Initialize()

    Dim iRow As Long
    Dim myArray As Variant

    myArray = Worksheets("sheet1").Range("a2:b20")

    With Me.ComboBox1
    .ColumnCount = 2
    .BoundColumn = 2
    .ColumnWidths = ".5 in; .5 in"
    .List = myArray
    End With

    End Sub

    Since I set the boundcolumn to 2, I get the second column returned. (You could
    even hide the second column by using: .ColumnWidths = ".5 in; .0 in"

    But the value is determined by the .boundcolumn.

    Private Sub CommandButton1_Click()
    MsgBox Me.ComboBox1.Value
    End Sub

    If you can't pick it up nicely, you can add them one at a time:

    Private Sub UserForm_Initialize()

    Dim iRow As Long
    Dim myCell As Range

    With Me.ComboBox1
    .ColumnCount = 2
    .BoundColumn = 2
    For iRow = 1 To 20 step 3
    Set myCell = Worksheets("sheet1").Cells(iRow, 1)
    .AddItem myCell.Value
    .List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value
    Next iRow
    End With

    End Sub

    Another option may be to just put the one column in and then use some kind of
    lookup to determine the other value.




    df wrote:
    >
    > Hi I need to populate an excel combo box with recordset
    > fields i already retrieve from an SQL Database. I need to
    > columnes code and description. For example: Employee ID
    > and Employee Lastname. Y need to show the Lastname in the
    > combo and retrive the selected Employee ID from it.
    >
    > I tried
    > ComboBox.AddItem()
    >
    > But I could only add an item with one field. How can I add
    > the couple Employee ID - LastName? (I discard the
    > concatenation method).
    >
    > Regards
    > Daniel


    --

    Dave Peterson
     
    Dave Peterson, Jul 9, 2003
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ehibinitie

    Excel - Populating blank columns

    ehibinitie, Mar 4, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    157
    Andy B
    Mar 4, 2004
  2. Guest
    Replies:
    1
    Views:
    167
    Guest
    Mar 24, 2005
  3. Bre-x

    vba function ( populating rows)

    Bre-x, Jul 11, 2007, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    156
    Guest
    Jul 13, 2007
  4. pallaver

    .AddItem list and populating combobox with created list

    pallaver, Jun 26, 2008, in forum: Microsoft Excel Misc
    Replies:
    8
    Views:
    383
    Dave Peterson
    Jun 27, 2008
  5. JimC
    Replies:
    2
    Views:
    664
Loading...

Share This Page