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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.
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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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
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).
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.
Martin LissOlder than dirtCommented:
Try this.
29072884.xlsm
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
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.
Martin LissOlder than dirtCommented:
Updated.
29072884a.xlsm

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
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
AndreamaryAuthor Commented:
Thanks, Martin, I’ll follow up...

Cheers,
Andrea
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.