I am trying to create a project tracker document for my team to use. This will be an Excel worksheet where we have a table set for the main focus of the work, and as we type below the last row, the typed text turns into a new row of the table (this is working just fine, my question is below):
I have data validation in 5 or so columns in the table (4/5 use a list of “yes” or “no” and the last column uses “None” “Scheduled” and “completed”). I want to write/use a VBA that sets each of these to be a default value (“No” or “None” depending on the column) and then when a new row is added to the table, these are inserted as the default as well. Can someone help clarify how this works for me?
Here is an image of the sheet so far with some test information entered if this helps.
I have used a few VBA examples found online but they were causing troubles in that they either set all 5 columns to the same specific value (“No”, which worked for 4/5 of the columns but not column 5) and none of them carried over to the new row unless I ran the macro again, which I am trying to avoid as I will have a team of folks using this and don’t want to rely on them always following through with that.
Cory Unthank is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Set up your data (simplified view):
Click on Insert –> Table, select the option “My table has headers”:
Click on the cell where you want your data validation and click on Data–>Data Validation. Created whatever rules you require. As new rows will be added to the table they will inherit the data validation rules set for the given column: