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:
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
- 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
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":
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?
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...