Dynamic named range question related to Excel user form combo box

I've created an Excel user form to track time spent on projects. Each employee will have their own User Form workbook. Each workbook will have a 'Tasks' list linked to a single Master Project List so that everyone has the same list of projects within their User Form workbook.

DESCRIPTION OF WORKBOOK WITH USER FORM:
User form: Opens when workbook opens, or when 'Add a record' button is clicked, and is the form employees use to enter the project name and # of minutes worked on it. The 'Task' field is a combo box, pulling from a dynamically named range as described under 'Tasks' tab below.
Tracking sheet tab: A hidden spreadsheet that is updated by entering records in the User Form
Tracking view tab: The employee view (read-only) of records, populated from the hidden tracking sheet as described in previous line
Tasks tab:
  • Linked to a 'Master Project List' spreadsheet so it will update whenever new projects are added to the Master Project List
  • There is a dynamic named range on the Tasks tab called "TaskList" that serves as the dropdown list for the User Form's combo box called cboTask. It includes a number of 'blank' linked rows originating from the Master Project List that serve as placeholders so that the list of projects in the Tasks tab will update as soon as they are added to the Master Project List
  • The Master Task List resides as a separate workbook, but for the purposes of posting this question on EE, I have added it as a tab to this workbook
Blank rows in dropdown of combo box
CHALLENGE:
On the User Form is a combo-box listing the projects. Because of the dynamic named range including the blank placeholder rows as described above, these rows show up in the drop down box and can be accidentally selected. When a blank row is accidentally selected, it populates the following fields with "0":
  • Task
  • ID
  • Product/Unit

Zeroes in fields when blank row selectedDESIRED SOLUTION(S):
Is there any way that the dynamic named range on the 'Tasks' tab called TaskList can be set to ignore the rows that have links but are not populated with data, so that the combo box dropdown on the User Form is limited to showing the projects, and not also all the blank placeholder rows?

Task list
If the above is not possible, then is there a way to update the existing error-handling for the 'Save' button such that if the cboTask = 0, then it would trigger the error message? I attempted this myself, but did not have success.

Please let me know if you have any questions or need clarification on any details...

Thanks!
Andrea
Task_Plan_AC.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.

Mason BangerterSystems AdministratorCommented:
Where is the data in the Master Task List coming from, is it manually inputted?
0
AndreamaryAuthor Commented:
Hi Mason,

Yes, the Master Task List is manually updated as new projects come online...

Cheers,
Andrea
0
Roy CoxGroup Finance ManagerCommented:
The blank rows are a poor idea. Why not simply sort the source as it is entered.
0
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.

Roy CoxGroup Finance ManagerCommented:
I've just looked at your WorkBook and you have a Table for the Tasks, so I can see no advantage in using a Named Range. Tables are Dynamic anyway.

Providing that the format is correct then this will do what you want
Task_Plan_AC.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I've amended the code for the Status combo, note I have removed all unused rows in the Table.

Now the zeros do not appear in cboStatus

Also, removed TaskList
Task_Plan_AC.xlsm
0
AndreamaryAuthor Commented:
Hi Roy,

Thanks for your replies. I've tried out the updated file, and unfortunately ran into a few issues.

The 'Tracking View" tab (not the "Tasks" tab) is meant to be the active spreadsheet for the employee. It provides read-only views of their saved records, and has the button to re-activate the User Form if/ as needed (as the employee can choose to cancel the User Form to view their read-only records). When I opened the file, the tab that was active was the "Tasks" tab. When I switched to the "Tracking View" tab and clicked on 'Add a Record' button to open the User Form and select an item from the Task drop down, the drop down menu did not contain the list of tasks, as shown below:

Missing-dropdown.PNG
I then tested the User form, and when I tried to save a record, I received an error message "Run-time error 438". The default date in the user form was 30-Dec-99 instead of the current date (is that actually a zero value in a date format, which perhaps is causing the error message?).

Runtime-error-and-date-field.PNG
Please let me know if you have any questions about my feedback...

Thanks,
Andrea
0
Roy CoxGroup Finance ManagerCommented:
I've only answered the question relating to the combobox. I haven't changed or looked at the other code.I'll have a look at the rest of the code.

The workbook opened on the last used sheet, which is how Excel works. If you need it to always open on a specific sheet then you require code to force that to happen.
0
Roy CoxGroup Finance ManagerCommented:
Is this right now?
Task_Plan_AC.xlsm
0
AndreamaryAuthor Commented:
Hi Roy,

Thanks for your helpful tip on ensuring that the spreadsheet opens on a specific tab.

I opened your latest file, and the default date is working fine now - thanks!

 I then tested out entering a record, and noticed that unfortunately the drop down for the combo box is still problematic, as shown in the screen capture below. I've also included a second screen capture of how the drop down should look:

Latest-dropdown.PNG
Andrea
0
Roy CoxGroup Finance ManagerCommented:
Slight modification made. I hadn't allowed for you opening the userform on a different sheet.

Note the other Tables don't need those empty rows.
Task_Plan_AC--1-.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I,m not a big fan of hiding the Application but I've added an error handler in case problems occur loading the form. Without this if an error occurs then the application will remain invisible. I can't actually see any need for it in the Open event of the userform.

Also, your code opens the userform to the right of my screen, is this what you intended?
0
AndreamaryAuthor Commented:
Hi Roy,

Yes, having the userform open to the right is intentional. Thanks for your replies. I just got back from being out of town so haven’t had a chance to review your most recent feedback, but will have time tomorrow evening to do so and get back to you...

Cheers,
Andrea
0
Roy CoxGroup Finance ManagerCommented:
No problem
0
AndreamaryAuthor Commented:
Hi Roy,

I'm sorry about the delay in getting back to you. I tested out the latest file, and there are a couple of issues:

1. I am receiving a runtime error when trying to save a record.
2. The combo box still shows the blank rows below the list of tasks, which can be selected, populating 3 fields with zeroes.

Runtime 438
Blank rows
I do appreciate your efforts, Roy. I suspect that how I have set this workbook up in the first place is causing problems that may not be fixable...? My idea of setting up the combo box that references a dynamically named range, that in turn links to an external spreadsheet may be fundamentally problematic. I am not savvy enough in Excel to determine this, but at this point maybe it's best to abandon my idea. Perhaps you can advise?

Thanks,
Andrea
0
Roy CoxGroup Finance ManagerCommented:
the debugging error occurs because you haven't assigned a value to NextRow before saving.

The empty rows are because you have empty rows in the source Table, the only way around this would be to have a separate table for each ComboBox
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 for all your help with this, Roy. I have the answers I need to proceed...

Cheers,
Andrea
0
Roy CoxGroup Finance ManagerCommented:
Let me know if you need further help. I can split the Table and use two for populating the comboboxes if you want.
0
Roy CoxGroup Finance ManagerCommented:
I've split the Table and removed the empty rows on the ComboBox
Task_Plan_AC--1---3-.xlsm
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.