CODEWITHSUNDEEP
excelvbalistbox
Liam Lenny Leonard
Liam Lenny Leonard

Reputation: 33

VBA Listbox - Selection Text Issue

Would there be anyone out there that could point me in the right direction with a system I am making within vba (excel based).

I currently have a userform with a multi col listbox selection, the listbox is populated from the database. But when you go to select the item on the listbox it only shows the first col. How would I be able to make it so it showed the first and seconded col. Please see below images

Listbox when open

Listbox when selected

form_create_order.Controls("cmb_product_category").Enabled = True

Dim Connection As New ADODB.Connection
Dim rst     As New ADODB.Recordset
Dim rcArray As Variant
Dim sSQL    As String

With Connection
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = DatabaseLocation
    .Properties("Jet OLEDB:Database Password") = ProtectPassword
    .Open
End With

sSQL = "SELECT ID, Category_Name " & _
    "FROM Category_Database ORDER BY Category_Name;"

rst.Open sSQL, Connection
rcArray = rst.GetRows

With form_create_order.cmb_product_category
    .Clear
    .ColumnCount = 2
    .List = Application.Transpose(rcArray)
    .ListIndex = -1
End With

rst.Close
Connection.Close
Set rst = Nothing
Set Connection = Nothing

Upvotes: 1

Views: 145

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

I'd suggest to follow the MSDN documentation: How to: Change the Column Widths of a Multi-Column List Box

ListBox1.ColumnWidths="0;500" 'replace `;` with `,` if your system uses commas as a separator


[EDIT]

Accordingly to PEH comment, you can use:

ListBox1.ColumnWidths = Replace$("0;500", ";", Application.International(xlListSeparator))

if you're looking for more flexible solution, independent of regional settings.

Upvotes: 1

JvdV
JvdV

Reputation: 75850

For the sake of giving alternatives (found here):

Private Sub ComboBox1_Click()

With ComboBox1
    .Text = .List(.ListIndex, 0) & " | " & .List(.ListIndex, 1)
End With

End Sub

No need for columnwidth in this case (however, that is a more elegant way).

Upvotes: 1

Related Questions