First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
for me) "List1".

I defined that name using this:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
So that it would expand and contract based on the number of entries in column A
of Sheet2.

Debra Dalgleish has instructions:
http://www.contextures.on.ca/xlNames01.html#Dynamic

Then I applied data|validation to A1 of sheet1.
I chose List and used List1 as the range for the list.

But on the Error alert tab of the Data|Validation dialog, I chose:
Style: Warning
Title: New Entry!
Error Message: New entry will be added to list if you click ok.

Then I used a worksheet_change event that waited for a change to A1. Note that
this will not work in xl97. (But Debra shows away around it using a button near
the dropdown.)

This was the code behind sheet1:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myList As Range

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1,b9,c3")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

Set myList = Nothing
Select Case LCase(Target.Address(0, 0))
Case Is = "a1"
Set myList = Me.Parent.Worksheets("sheet2").Range("list1")
Case Is = "b9"
Set myList = Me.Parent.Worksheets("sheet2").Range("list2")
Case Is = "c3"
Set myList = Me.Parent.Worksheets("sheet2").Range("list3")
'etc
End Select

If myList Is Nothing Then
Exit Sub
End If

If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
'already there, do nothing
Else
With myList
.Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
Set myList = .Resize(.Rows.Count + 1, 1)
End With

With myList
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
End If

End Sub

It seemed to work ok.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm


cjtj4700 wrote:

Hi~
I have been hooked up with a very nice VLookup tool for my companies
shipping request form (thank you Gord!). Several "Lists" of data is stored in
Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
would like to know if anyone out there would be willing to send me the code
or let me send them my workbook to allow new entries into any dropdown cell
to be auto added to its source list, alphabetically. Per Contextures.com,
there is a macro that enables this and VB does a fly by on me. The sample
spreadsheet I was looking at was "Update Multiple Validation Lists".

Thanks as always!

--

Dave Peterson