CODEWITHSUNDEEP
ms-accessvba
emihir0
emihir0

Reputation: 1250

Selected listBox row columns referenced by listBox column head name instead of column index number

How do I do this? I have a quite complex listBox, containing 12 columns in total and want to fill out some textBox fields in my form upon listBox_Click().

At the moment I do it such that:

With Me
    .txtBox1 = Me.lstBox.Column(0)
    .txtBox2 = Me.lstBox.Column(1)
    .txtBox3 = Me.lstBox.Column(5)
    .txtBox4 = Me.lstBox.Column(10)
End With

However, this can be very confusing when I add/remove a specific column from the listBox. VBA would not let me reference the Column in this way:

Me.txtBox1 = Me.lstBox.Column("Price")

Where "Price" is basically the Column header that displays in my lstBox.

Upvotes: 1

Views: 625

Answers (1)

Gustav
Gustav

Reputation: 55816

You don't. Or rather, if you insist, you will first have to create a VBA Collection with the coloumn name as key and the index as value. Then you can do like this:

Me!txtBox1.Value = Me!lstBox.Column(CollectionOfColumns("Price"))

Or, if the series of columns is fixed, you can create an Enum and use:

Me!txtBox1.Value = Me!lstBox.Column(EnumOfColumns.Price)

Upvotes: 1

Related Questions