Dynamic Data Validation
Data Validation is a nice feature for restricting cell inputs, but sometimes you want the user to be able to add an item to the DV list. With the Worksheet_Change event and a dynamic range name, you can allow the user to do just that.
First, set up a dynamic named range for your list
Note the first item in the list is "(new entry)". This will be used to trigger the macro that allows the user to add to the list. Next, create the data validation
Finally, create the code in the Change event to catch when the user selects (new entry).
Application.EnableEvents = False
Dim vResp As Variant
Dim sTestValid As String
'Make sure the cell has validation
On Error Resume Next
sTestValid = Target.Validation.Formula1
On Error GoTo 0
'If the validation refers to our list and the user
'selected New entry
If sTestValid = " = ValList" Then
If Target.Value = "(new entry)" Then
'Get the new value from the user
vResp = InputBox("Enter new item", "New Entry")
'If the user didn’t click cancel
If Len(vResp)> 0 Then
'add the new entry to just below ValList
With Me.Range("ValList")
.Cells(.Cells.Count + 1).Value = vResp
End With
'Set the cell to the new entry
Target.Value = vResp
Else
'If the user cancelled, clear the cell
Target.ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub
You can beef up the code to make sure the user is entering something reasonable. The code can also be modified for use with a hard-coded list, as opposed to a range. But I have to leave something for you to experiment with.
Andy Miller:
That's an interesting bit of code. I'm surprised that
With Me.Range("ValList")
.Cells(.Cells.Count + 1).Value = vResp
End With
doesn't cause an error, though. Wouldn't it be trying to access a cell that is not actually in that range? Or is it such that with the dynamic named range, you can get away with a bit?
30 June 2004, 12:19 pmDick:
Andy: That's a subject for a post that I've been meaning to do. Range is just one of those objects that's really a collection, but not really it's an object - got that. It's like the Item property applies to the whole sheet even when used with just a range. It's strange.
30 June 2004, 3:27 pmAndy Miller:
So, in other words, even if a range contained 5 cells, you could theoretically write to a 6th or 7th cell in that range?
30 June 2004, 3:50 pmJuan Pablo:
I knew I had read it at Chip's site:
http://www.cpearson.com/excel/cells.htm
30 June 2004, 4:32 pmBrian V:
If your dynamic range is on another worksheet, then you'll get an error. A few additions to determine which worksheet contains the dynamic range:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim vResp As Variant
Dim sTestValid As String
Dim valShtName As String
'Determine the worksheet with the list
valShtName = ValSht("ValList")
'Make sure the cell has validation
On Error Resume Next
sTestValid = Target.Validation.Formula1
On Error GoTo 0
'If the validation refers to our list and the user
'selected new entry
If sTestValid = "=ValList" Then
If Target.Value = "(new entry)" Then
'Get the new value from the user
vResp = InputBox("Enter new item", "New Entry")
'If the user didn't click cancel
If Len(vResp) > 0 Then
'add the new entry to just below ValList
With Sheets(valShtName).Range("ValList")
.Cells(.Cells.Count + 1).Value = vResp
End With
'Set the cell to the new entry
Target.Value = vResp
Else
'If the user cancelled, clear the cell
Target.ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub
1 July 2004, 8:20 amFunction ValSht(rngName As String) As String
On Error Resume Next
ValSht = ThisWorkbook.Names(rngName).RefersToRange.Parent.Name
End Function
Matt H:
Maybe I'm missing something, but the whole "dynamic range" thing (for a data validation source or chart data source) seems clever but overly complicated to me.
If you've got a list in the range C1:C5 but think you or someone else will later add an entry to it, why not just enter under source: C1:C100?
1 July 2004, 10:32 amJuan Pablo G:
Matt,
Because that creates a *very* long list with *a lot* of empty options... not very pretty, and sometimes confusing for the user.
And Brian, you could just replace
Me.Range("ValList")
with
Me.Parent.Names("ValList").RefersToRange
because ValList is a workbook name.
1 July 2004, 11:37 amAndy Miller:
Actually, the blank lines do not show up in the Validation Listbox.
1 July 2004, 12:29 pmJuan Pablo G:
Yep... they won't... I could swear the did ! but nope, thanks for the correction Andy.
1 July 2004, 4:10 pmAndy Miller:
No problem. To be honest, I thought that they did too, but I must be remembering the older listbox/combobox controls that I've used, rather than validation dropdown boxes.
I figured I'd test my thought before posting, and it turned out that my initial thought, like yours, was wrong. I wonder if it's the same for earlier versions of Excel.
2 July 2004, 7:57 amDavid Landy:
Thanks for a great piece of code!
As an extension, if you want the newly-added items to appear in the combo, add a line of code to extend the range after adding the new entry:
With Me.Range(sRange)
.Cells(.Cells.Count + 1).Value = vResp
'resize the range
.Resize(.Rows.Count + 1, .Columns.Count).Name = sRange
End With
Hope this helps someone out there!
David.
4 August 2004, 9:07 amSandy W:
I have the source of the list at C1:C10 and then apply the validation list to A1:A10. If, then I put item 1 to A1, I want the rest cells (A2:A10) doesnt include item 1 in the validation list. And so in A2 with item 4, then range A3:A10 doesnt include item 1 and item 4? When i filled up 9 cells, the last cell will only provide one option, the last item. Is it possible to do that?
4 May 2007, 4:09 amFlogsta:
Hi,
Great webpage!
I do follow all steps.
Can not get it to run.
If I choose (new entry)... (new entry) will be typed...
Thanks for help.
25 October 2007, 1:39 pmFlogsta:
Never mind.
The fix was... copy the code first into WORD and from there into VB..
for some reason the " signs were not right when pasting directly into VB.
Again, awesome webpage with great people!
25 October 2007, 1:48 pmDick Kusleika:
Thanks Flogsta. I upgraded it to the new VBA-showing-scheme so the quotes should paste directly now.
29 October 2007, 9:41 amFlogsta:
Does this work if the LIST is on another worksheet?
I have the drop-down worksheet A and the list on B. Everything works fine except when I select (NEW ENTRY). I won't let me add any new entry.
First I get an ERROR Message and after that no error message but (NEW ENTRY) will be typed into the cell.
Thanks for your help and input.
30 October 2007, 9:53 am