add item to a drop-down list

[NazRem]

Hi!
How can a drop-down validated cell (data validation) be automaticaly updated
whem i add an item to the original list.

 

[Gary''s Student]

A very tiny trick:

When you insert the new item in the Validation List, insert it in the middle
rather than the end. This way you avoid re-defining the list range.

 

[NazRem]

Thank you, but
can't the re-defining the list range be done automatically when i add an
item to the last available cell of the column? (I want to compare an entry
with the list and if it is not there I want to includ it using a macro)

 

[Gary''s Student]

In this sample, A1 is the cell with validation; column D is the list. This
worksheet event macro detects changes made to column D and re-creates the
validation for A1:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set d = Range("D")
Set a = Range("A1")
If Intersect(t, d) Is Nothing Then Exit Sub
n = Cells(Rows.Count, "D").End(xlUp).Row
Application.EnableEvents = False
a.Validation.Delete
a.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$D$1:$D$" & n
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

Maybe you could use a dynamic range name.

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic