add item to a drop-down list
 

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

add item to a drop-down 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.
--
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.

add item to a drop-down list
 

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.

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.

add item to a drop-down list
 

Maybe you could use a dynamic range name.

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

NazRem wrote:

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

--

Dave Peterson