Clara,
Since any item within a predefined list that gets changed should show up
with the change in the data validated cell(s) as soon as the change is made,
I am assuming that you have extended the list rather than just changing an
existing entry?
Let's say your list was in E2:E7 on a sheet and the validated cell was B1.
In the data validation setup for B1 it would show =$E$2:$E$7 as the list
source. You then add to the list with an entry into E8 and it isn't in your
list. However, had you gone into your list and inserted a new cell (or row
if it doesn't mess up the rest of the sheet) within the range (select E7 and
use Insert to put in a new cell/row, and make your entry in it, then B1's
list will show all entries from E2:E8 automatically.
Another way to deal with dynamic lists used for data validation is to give
the list a name, making it a named range. Using our example above, you could
choose E2:E7 and give it a name like myListOfStuff and then when you set up
data validation for a cell such as B1, for the source you would enter
=myListOfStuff
This has a couple of advantages:
#1 - your list no longer has to be on the same sheet with the validated
cells using it. You can even move it to a hidden sheet for neatness and to
keep prying eyes off of it.
#2 - you can dynamically alter the list by inserting/deleting and never have
to worry about whether or not the validated list shown is current or not - it
will be as long as the list is managed properly.
If you truly need a programming solution for this, we'll need some more
information: where does the list reside (addresses of cells in the list) and
addresses of the cells that use the list for validation.
In the meantime, here is some rudimentary code that would change the
validation list range on a group of cells automatically. It is worksheet
code, right-click the worksheet's tab and choose [View Code] and copy and
paste this code into it, changing references to columns involved as required.
Private Sub Worksheet_Change(ByVal Target As Range)
'update Data Validation parameters for cells
'based in a change in the Source list
'Sample only - addresses must be changed
'to match reality of your situation
'
Dim valListFormula As String
Dim valCellsRange As Range
'assumes list is in column X and
'nothing else is in that column
'test to see if change occured in X
If Application.Intersect(Target, Range("X:X")) Is Nothing Then
Exit Sub ' no change in column X
End If
'assumes list starts at row 1 and continues down
valListFormula = "=$X$1:" & _
Range("X1").End(xlDown).Address
'set up reference to the cells that
'use the list as their Source
Set valCellsRange = Range("C1:C10")
'redefine the validation for all cells
'in range valCellsRange
With valCellsRange.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=valListFormula
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
