Populating Excel user form with value from second column of multi-column combobox dropdown

The user form called TimeTracker has a combobox, called cboTask, that has a multi-column dropdown menu. This is to assist users with providing more context to their selection, especially as the dropdown list grows.

Here is a description and issue:

Name of user form: TimeTracker
Combobox under label called 'Task': cboTask
3-column dropdown: From dynamic named range on sheet called "Tasks", which includes Col A (ID), Col B (Task), Col C (PA Unit)
Issue with cboTask: Currently populating the 'Task' field in user form with value in Col A (Tasks!ID)
Fix: Would like this field to be populated instead with value from Col B (Tasks!Task)

I have included the spreadsheet.

Thanks,
Andrea
TimeTracker-EE.xlsm
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
In Formulas Tab->Name Manager, select the TaskLIst named range and change the Refers To formula to

=OFFSET(Tasks!$B$2,0,0,MAX(COUNTA(Tasks!$B:$B),COUNTA(Tasks!$C:$C)),3)

if you want to see Task, PA_Unit and Date, or to

=OFFSET(Tasks!$B$2,0,0,MAX(COUNTA(Tasks!$B:$B),COUNTA(Tasks!$C:$C)),2)

if you want to see Task and PA_Unit.
0
AndreamaryAuthor Commented:
Thanks, Martin, for your quick response. I tried it, and noticed that I don't see the ID number any longer in the dropdown list. Sorry, I should have mentioned that I'm looking for a solution, if possible, that retains the same columns in the original dropdown, and that VBA code can somehow select the value from the second column.

Cheers,
Andrea
0
Martin LissOlder than dirtCommented:
I'm confused since if you go back to the original formula it seems to be doing that already.

Microsoft_Visual_Basic_for_Applicati.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AndreamaryAuthor Commented:
Yes, it appears that the spreadsheet is being populated correctly. It’s the user form display that is my problem. When the user selects the dropdown menu for Task, the user form fields for both Task and ID are both populated with ID. On the user form, I would like, if possible, for the Task field to be populated with the value in the Task column (column 2) instead of the value from the ID column (column 1).

Hope that makes sense...?

Cheers,
Andrea
0
Martin LissOlder than dirtCommented:
When the user selects the dropdown menu for Task, the user form fields for both Task and ID are both populated with ID.
Are you seeing the same thing I am? It looks to me like it's already doing what you want. If it's not then please tell me what should appear, for example, in the first row of the combobox display.
Cursor_and.jpg
0
AndreamaryAuthor Commented:
Hi Martin,

The user form view is the issue, not the spreadsheets, as currently two user form fields have the same value (ID).  I'm hoping that there is VBA code that, while maintaining the current 3-column dropdown for the user, can populate the Task field in the user form from column 2 (Task) instead of column 1 (ID). I already have the ID field below, which is auto-populated based on the user's selection in the Task combobox:

Fields on user form with duplicate values (ID).
0
Martin LissOlder than dirtCommented:
Sorry that it took me this long to understand but I think I do now. I have to go out but I'll be back in a couple of hours.
0
Martin LissOlder than dirtCommented:
Try this.
29072884.xlsm
0
AndreamaryAuthor Commented:
Hi Martin,

Good news, and a couple of issues...

The good news is that the user form is now populating correctly - thank you! The 'TaskList' range, however, appeared to have been changed from referencing the 'Tasks' sheet (which was hidden, and is a unique list of the tasks), to the TrackingSheet. As a result, the dropdown was showing duplicate tasks and was out of order, etc. I changed the 'TaskList' range back to the 'Tasks' sheet, and that reverted the dropdown list to an ordered list of unique tasks, which was good. However, I noticed that when I now add new records, Column B (the task description) of the TrackingSheet remains blank. All the other columns are populated correctly.

So the upshot is:
The user form fields are being populated correctly
The TrackingSheet is being populated correctly with the exception of Column B, which is blank

Thanks for your efforts on this, and I'm hoping that the TrackingSheet issue can be resolved...

Cheers,
Andrea
0
Martin LissOlder than dirtCommented:
I didn't realize that the Task sheet existed and that's why I changed the TaskList named range. I'll be back.
0
Martin LissOlder than dirtCommented:
Updated.
29072884a.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Thanks so much, Martin, this is perfect! And I apologize for neglecting to mention the hidden Task sheet - out of sight, out of mind. :-(

I like the way you enhanced the Task combobox dropdown by adding a heading row. Thanks for going the extra mile!

Cheers,
Andrea
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
AndreamaryAuthor Commented:
Thanks, Martin, I’ll follow up...

Cheers,
Andrea
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.