If you are using data validation (and you should) to help you with your data entry, then you might find that the list you are using doesn't have all the values you want.
For example, if you were using a list to assign a priority to some work, your list may consist of 'Not Started', 'In Progress' and 'Completed'. But later on you realise that you need to add 'On Hold' and 'Cancelled'.
In my case I'm putting a roster together of who's making me a cup of tea, and I'm using a list of staff names: Amanda, Gary, Hannah and Xavier. A few weeks later I hire Bruce and Kylie and I want to add their names to the list.
Normally you'd just go to the source table and add these names, and you can still do that, but with this code it's even easier. You just type in Bruce, and then Kylie, and their names are automatically added to the source data validation list.
Both Bruce and Kylie then become available to use in the drop down list.
And for even more coolness, after I've added the new names, I sort the list alphabetically.
Let's back up a bit though and look at how we put this all together.
The Code
Enter your email address below to download the sample workbook.
Download this Excel Workbook containing all the code in this blog post. Note: This is a .xlsm file please ensure your browser doesn't change the file extension on download.
This may seem a strange thing to be doing.
The use of data validation is to limit the choices and therefore control what is entered.
So to allow someone to add to that list without checking what is entered goes against the whole point of data validation lists.
But, we have had requests to do this so here is a working example.
Just bear in mind that if you release this to users they can add whatever they want to your DV lists.
But if it is for use just by yourself (or trusted others) then you may find this very useful.
Tables
Let's start with tables. By using tables I can use VBA methods and properties for tables and this makes the job easier than other approaches.
Set up your source table, and populate it with your staff names (or whatever you are using). Then set up the output table similarly.
Set Up Data Validation List Source
Next we need to configure our source list which will be used for the data validation.
There are a few quirks when using tables as the source for data validation lists so please read that article if you are not familiar with them.
The name of my source table is Persons, and to get around the aforementioned quirks, I've opted to create a 2nd name for my source table, Person_Table, and use this when setting up the data validation.
My roster of tea makers is a table called RosterTable.
That's all I need to do in the sheet, the rest is in the VBA.
Writing the VBA
Firstly, I'm using a Worksheet_Change worksheet event that triggers every time the sheet changes.
When this code executes, I check if the active cell (the one that just changed) is in my RosterTable. I do this by checking if the Target (the active cell) is in the ListObject (table) named RosterTable
If Target.ListObject.Name <> "RosterTable"
I also have to check a couple of other things, both of which would mean exiting the VBA code and doing nothing. The first thing to check is if the value in the changed cell is empty
Target.Value = ""
The second thing is if the changed cell is the header row of the table RosterTable
(Not Intersect(Target, Target.ListObject.HeaderRowRange) Is Nothing)
If either of these conditions are true, the code exits. Putting it all together it looks like this
On Error Resume Next
' IF the cell that has just changed (Target) isn't in our Output Table OR
' if that same cell is blank OR
' that same cell is the header of our Output table THEN
' turn default error handling back on and exit the sub
If Target.ListObject.Name <> "RosterTable" Or Target.Value = "" Or (Not Intersect(Target, Target.ListObject.HeaderRowRange) Is Nothing) Then
'Turn default error handling back on
On Error GoTo 0
Exit Sub
End If
'Turn default error handling back on
On Error GoTo 0
Error Handling
You'll notice that I've turned off default error handling
On Error Resume Next
Because checking the value of Target.ListObject.Name can result in an error if the Target cell isn't in a table (ListObject), I need to allow for this and prevent Excel generating an error.
Once I've done this check I can turn default error handling back on
On Error GoTo 0
Checking for a New Name, and Adding it to the List
Once we confirm that the changed cell is in the RosterTable we check if a new name has been typed in. This is really easy to do using the Find method
If .Range("Persons").Find(Target.Value) Is Nothing Then
So, if the Find method looks for the value of the target (changed) cell in the range "Persons" (the Persontable), and finds nothing, we have a newly typed in name.
Let's add it to our PersonTable
With .ListObjects("Persons")
.ListRows.Add
.ListRows(NameTable.ListRows.Count).Range.Cells(1, 1) = Target.Value
and sort the list of names
With NameTable.Sort
.SortFields.Add NameTable.DataBodyRange.Cells(1, 1), xlSortOnValues, xlAscending
.Apply
.SortFields.Clear
End With
and that's it.
New Charts in Excel 2016
Simon Kerr (Chef Patron)
Hi Phil & Mynda
I find this VBA tool SUPER helpful in my monthly accounts for purchses and sales invoices as I have customers / suppliers who are not on my “MASTER LISY” (read DATA VALIDATION LIST) but are probably only going to appear for a month or 2 or maybe longer. This is a great way to add them to my list which leads up to the problem I have encountered.
I create a new WS for each new month…obs but in copying the WS from my MASTER WS the table names up date each month from TBL_INVOICES on my MASTER WS to say TBL_INVOICES50 on my July 2023 WS
How can I update the VBA code automatically? Currently I go into the code and edit line# manually each month.
EG “If Target.ListObject.Name “TBL_INVOICES50” Or Target.Value = “” Or (Not Intersect(Target, Target.ListObject.HeaderRowRange) Is Nothing) Then”
Philip Treacy
Hi Simon,
Thanks, great to hear you find it useful.
You could try a couple of things.
At the top of the VBA, declare a string for th thing that changes e.g.
DIM MyTable as String
then set that value
MyTable = “RosterTable”
and in the code replace and explicit mention of specific table name to the new string value. This way you only need to make 1 update at the top of the code.
As an extension to this, you could use cells on the sheet to store the names of tables. So let’s say you enter Rostertable into A1.
In your VBA you can have this
MyTable = Range(“A1”).Value
an this way you wouldn’t have to open the VBA to modify it (once the initial changes are made) for the new table name.
regards
Phil
Simon Kerr
Hi Phil
This is something I’ve been working on (rather unsucessfully I may say). I need to update a Master List of SUPPLIERS on a monthly basis, which is on my DATA worksheet. I have several input columns on other worksheets that acess this using Dynamic drop downs (=FILTER(Table_SUPPLIERS[SUPPLIERS];ISNUMBER(SEARCH(A1;Table_SUPPLIERS[SUPPLIERS]));”NOT IN LIST”) where cell A1=CELL(contents).
Is it possible to get the VBA to look at ANY change in the workbook?
kind regards Simon
Philip Treacy
Hi Simon,
Not sure I follow what you are trying to do but you can check for any changes using events like Worksheet.Change or Workbook.SheetChange
Workbook and Worksheet Events in VBA
Regards
Phil
kabir
I added new data “Spanner”, then list update. But When I add “Span”, excel shows format error and list dosen’t update. Using Office365
Philip Treacy
Hi Kabir,
There was a bug in the code. I’ve fixed it now so you can download the workbook again.
Regards
Phil
Mike Lavelle
With a little tweaking, this is an excellent hack for my field of work. I write long reports and many of reports have common and/or similar responses. I am envisioning that this solution can be used to replace my text expander by essentially automating an ad hoc database of many unrelated tables.
I currently use dozens of validation lists that are tied to Excel tables; and, any time I have a new response/item/whatever for any of these validation lists, I presently have to copy/paste that new string to the appropriate list. Obviously, this is not ideal.
Thank you for sharing this solution!
Mynda Treacy
Great to know you’ll be able to make use of it 🙂
Jomili
My “RosterTable” has multiple columns. If I do a change in ANY of the columns, my “Persons” table gets updated with that new value. How do I restrict the updates to only the “Name” column?
Philip Treacy
Hi John,
Insert this code just after the
at the top of the Worksheet_Change sub
If Target.ListObject.HeaderRowRange(Target.Column - Target.ListObject.HeaderRowRange(1).Column + 1) <> "Name" Then Exit Sub End IfWhat this does is check the column header for the target cell (the one being changed). If the header is not ‘Name’ then the sub exits.
Regards,
Phil
Jomili
Thanks Phil. I was previously trying to add it to the next IF statement below, but not having any luck. Your solution did the trick.
FYI that I added your IF statement as a single line, ending in “Exit Sub”, so didn’t need to conclude the “End If”, like so:
“If Target..Blah, Blah, Blah… Then Exit Sub
Philip Treacy
Hi John,
glad to help. Good work on producing more efficient code 🙂
Regards
Phil
René
Hi Phil,
I think this can be an interesting option that can be useful. However, there’s another downside. When you type the names (you know your team) rather than using the dropdown arrow (which can be faster then grabbing the mouse etc.), every error you make in typing will be added as a new entry in the DV. So typing Kulie in stead of Kylie gives you a new item. That certainly is not what you want.
cheers, René
Philip Treacy
Hi René,
Yes that is very true. So this needs to be used with caution. As I say in the post, this code should be used by you or trusted others.
Cheers
Phil
Glenn Case
Hey, Phil:
Thanks for the tutorial. I found it quite interesting. Actually, I have a good example of where this might be used. I have an action item list, and as team members are added, I used to adjust the DV list by hand. For me, the major consideration is not to restrict the choices so much as to ensure that the choices available are all spelled correctly so that they can be properly categorized.
With your code, just typing it in accomplishes the goal. I was a bit concerned about whether the same name with different capitalization would result in the name in the list twice, but a little checking shows that’s not the case (so to speak.) However, you can add the same name again by accident if a leading or trailing space is added to the name. To address that , I added
Target.Value = Application.WorksheetFunction.Trim(Target.Value)
right after the With Worksheets statement.
Thanks again for an interesting article!
Philip Treacy
Hi Glenn,
Thanks, glad you found this useful 🙂
Phil
Roger Govier
Hi Phil
Nice code to give people an easy way to add to their DV lists (with the caveat you have already made about this process).
However, I think that at the point where you turn off and on
Application.ScreenUpdating,
you should also do the same for
Application.EnableEvents
The code runs without adding this, but as soon as you Add a ListRow it triggers the whole event again, and the same as you then add the Target value into the newly added row.
Switching off Events stops this recursive behaviour.
Regards
Roger
Philip Treacy
Good catch Roger. You are right, I have added your piece of code.
Originally I had the source table on a different sheet so adding a row to it didn’t trigger the change event for my active sheet. But that is different now everything is on one sheet.
Thanks 🙂
Phil
Howard Silcock
This is an instructive example, but I have to point out that its goal seems somewhat bizarre. You set up data validation to have some control over what users can enter, then undermine that aim by letting them type in whatever they like – and it’s automatically accepted as valid! So why bother with the validation?
Philip Treacy
Hi Howard,
Yes you are correct. In this post I am only doing what was requested by one of our customers.
As you say, the use of data validation is to limit the choices and therefore control what is entered.
But if someone wants to add entries to that list without checking what they are, I can show how to do it, on the understanding of the limitations and problems this introduces.
Thanks
Phil
Steve
I found a much easier way to handle this is by using the Offset function. I normally have the list of options hidden and only those with access can add to the list. Using the Offset function makes it so my typically non-technical users don’t have to change the validation range.
=Offset(FirstItemOfMyList,0,0,COUNTA(ColumnOfData),1)
Mynda Treacy
Hi Steve,
I’m not sure which part you would use OFFSET for, but it can’t accomplish the same as the VBA does above.
Mynda