#1
|
|||
|
|||
Multi-select Combo Box with Item Descriptions
I have a spreadsheet which maintains a running log of training events in chronological order. Those events are tracked by a 11-digit training code in the rightmost column. An event can have multiple codes associated with it, so I need the ability to select all of the codes that apply. I recently followed the instructions from Aviral Mittal's webpage on how to create a multi-select listbox using VBA. It pops up a listbox which contains all possible training codes, and when I select the ones that apply to that event, it populates them in the appropriate cell in the log. Next step, I would like to add a second column of text to the listbox which contains a description of the training code, however I do not want the description to populate in the target cell. I already have a named range which contains the descriptions, I just need to figure out how to enter them into the listbox. Any idea on how I can accomplish this in VBA?
I'm posting a "cleaned up" copy of the workbook in question. When I click on a cell in the "Logbook" sheet, I want it to pop up a listbox with the choices populated from the "CODES" and "DETAILS" column on the "Codes" worksheet. Right now it only shows "CODES". However, when I select an item or items, I only want the target cell to show the value from the "CODES" column. |
#2
|
|||
|
|||
You don't need to loop through an array using AddItem to populate a listbox, you can make the listbox.list equal to the array,
and if the array is more than 1 column... so is the list but you need to adjust the column count property of the list box. This site is of interest for populating combo and list boxes. For what you've asked, I'd do this... In module1, replace Code:
Global gCodesListArr As Variant Global gDetailsListArr As Variant Code:
Global gCodesAndDetailsListArr As Variant Code:
gCodesListArr = Sheets("Codes").Range("A3:A67").Value gDetailsListArr = Sheets("Codes").Range("B3:B67").Value Code:
gCodesAndDetailsListArr = Sheets("Codes").Range("A3:B67").Value Code:
For Each element In gCodesListArr Me.ListBox1.AddItem element Next element Code:
Me.ListBox1.List = gCodesAndDetailsListArr |
#3
|
|||
|
|||
That did it, thank you for the help.
|
#4
|
|||
|
|||
Glad I could assist, and thanks for the feedback.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Userform: Select Item, Multiple MailMerge Fields Populate | ericav68 | Word VBA | 4 | 05-11-2019 06:29 PM |
Multi-lined combo-box values | wwwk | Word VBA | 1 | 05-09-2017 03:26 AM |
Multi-column combo box content control? | sugarjay | Word | 1 | 03-21-2017 07:20 PM |
Multi-select listbox help | gvibe@hotmail.com | Word VBA | 1 | 07-19-2013 10:54 AM |
Automatically select first item in drop-down? | flackend | Excel | 4 | 08-29-2011 02:07 PM |