add item to a drop-down list

  • Thread starter Thread starter NazRem
  • Start date Start date
N

NazRem

Hi!
How can a drop-down validated cell (data validation) be automaticaly updated
whem i add an item to the original list.
 
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.
 
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)
 
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: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
 
Back
Top