Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADD ITEM TO A DROP DOWN LIST? | Excel Discussion (Misc queries) | |||
How do I select more than one item from a drop down pick list? | Excel Worksheet Functions | |||
drop down list/typing item from list error | Excel Worksheet Functions | |||
How do I set up a drop down list to beginning letter of item? | Excel Discussion (Misc queries) | |||
multiple item entries from drop-down list | Excel Discussion (Misc queries) |