add item to a drop-down list
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
--
Gary''s Student - gsnu200823
"NazRem" wrote:
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" wrote:
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.
--
Gary''s Student - gsnu200823
"NazRem" wrote:
Hi!
How can a drop-down validated cell (data validation) be automaticaly updated
whem i add an item to the original list.
|