Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-29-2019, 02:11 PM
SLAB8002 SLAB8002 is offline Multi-select Combo Box with Item Descriptions Windows 10 Multi-select Combo Box with Item Descriptions Office 2013
Novice
Multi-select Combo Box with Item Descriptions
 
Join Date: May 2019
Posts: 2
SLAB8002 is on a distinguished road
Default 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.
Attached Files
File Type: xlsm Sanitized Logbook.xlsm (43.1 KB, 8 views)
Reply With Quote
  #2  
Old 05-29-2019, 07:32 PM
NoSparks NoSparks is offline Multi-select Combo Box with Item Descriptions Windows 7 64bit Multi-select Combo Box with Item Descriptions Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
with
Code:
Global gCodesAndDetailsListArr As Variant
In Worksheet_SelectionChange, replace
Code:
      gCodesListArr = Sheets("Codes").Range("A3:A67").Value
      gDetailsListArr = Sheets("Codes").Range("B3:B67").Value
with
Code:
      gCodesAndDetailsListArr = Sheets("Codes").Range("A3:B67").Value
In UserForm_Activate, replace
Code:
For Each element In gCodesListArr
  Me.ListBox1.AddItem element
  Next element
with
Code:
Me.ListBox1.List = gCodesAndDetailsListArr
Hope that helps.
Reply With Quote
  #3  
Old 05-30-2019, 07:10 AM
SLAB8002 SLAB8002 is offline Multi-select Combo Box with Item Descriptions Windows 10 Multi-select Combo Box with Item Descriptions Office 2013
Novice
Multi-select Combo Box with Item Descriptions
 
Join Date: May 2019
Posts: 2
SLAB8002 is on a distinguished road
Default

That did it, thank you for the help.
Reply With Quote
  #4  
Old 05-30-2019, 12:27 PM
NoSparks NoSparks is offline Multi-select Combo Box with Item Descriptions Windows 7 64bit Multi-select Combo Box with Item Descriptions Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Glad I could assist, and thanks for the feedback.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi-select Combo Box with Item Descriptions 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-select Combo Box with Item Descriptions Multi-column combo box content control? sugarjay Word 1 03-21-2017 07:20 PM
Multi-select Combo Box with Item Descriptions Multi-select listbox help gvibe@hotmail.com Word VBA 1 07-19-2013 10:54 AM
Multi-select Combo Box with Item Descriptions Automatically select first item in drop-down? flackend Excel 4 08-29-2011 02:07 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft