CODEWITHSUNDEEP
excelvbacombobox
user3906724
user3906724

Reputation: 7

How to display multicolumn combobox in Excel VBA?

I have written the code to populate the ComboBox and also set the BoundColumn and ColumnCount values to 2.

My requirement is to display material ID and material description in a ComboBox to make it user friendly.

But when I run the UserForm it is showing only second column (i.e material name). I also want to capture the selection and both values individually in two variables for further processing.

I am calling this macro in the initialize event of my UserForm.

Sub inittCode()

    Dim tcodelist() As String
    Dim tnamelist() As String

    'Activates master data sheet
    Worksheets("MD").Activate
    mylr = Cells(Rows.Count, 1).End(xlUp).Row

    'Calculate range size to initialize array
    Size = WorksheetFunction.CountA(Worksheets(1).Columns(1))

    ReDim tcodelist(Size)
    ReDim tnamelist(Size)

    'fill the first array
    For X = 2 To mylr
        tcodelist(X - 1) = Cells(X, 1)
    Next X

    'Adjust Array size to remove null values
    For a = 0 To mylr - 1
        tcodelist(a) = tcodelist(a + 1)
    Next a
    ReDim Preserve tcodelist(Size - 2)

    'Fill second array
    For x1 = 2 To mylr
        tnamelist(x1 - 1) = Cells(x1, 2)
    Next x1

    'Remove null values and adjust size
    For b = 0 To mylr - 1
        tnamelist(b) = tnamelist(b + 1)
    Next b
    ReDim Preserve tnamelist(Size - 2)

    'Fill combobox with array values
    MD_initform.matselcb.ColumnCount = 1
    MD_initform.matselcb.List = tcodelist
    MD_initform.matselcb.ColumnCount = 2
    MD_initform.matselcb.List = tnamelist

End Sub

Upvotes: 0

Views: 1406

Answers (2)

FunThomas
FunThomas

Reputation: 29146

When you want to display multiple columns you need to pass the list of items as 2-dimensional array. First index is for rows, second for columns.

Without understanding the details of your code, do something like

Dim tlist() As String
ReDim tlist(Size, 2)  ' Declare 2-dimensional array for "Size" rows and 2 cols
...
tlist(X - 1, 1) = Cells(X, 1)    ' Codes
...
tlist(x1 - 1, 2) = Cells(x1, 2)  ' Names

' Fill combobox with array values
MD_initform.matselcb.ColumnCount = 2
MD_initform.matselcb.List = tlist

Upvotes: 1

Jean-Paul
Jean-Paul

Reputation: 21

For multi-column ComboBoxes, Excel natively displays only 1 column, the first one or the .TextColumn one if not at default -1. I have coded a workaround module with 1 simple function that allows to display all the columns using Labels to cover the Combobox display area. Check this link and download the file. The Web text is in French (use any Web page translation extension/tool) and the VBA comments are in English.

Upvotes: 0

Related Questions