How to make a data validated field refresh
 

Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help

How to make a data validated field refresh
 

Not directly as it reflects a selected value, not a reference to a list
position. I think that you would have to write some VBA to trap the list
change and update the DV knowing what it was and where that was in the
original list.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"clara" wrote in message
...
Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help

How to make a data validated field refresh
 

HI Bob,

How can I trap the list change event, could you give me some clues?

Clara
--
thank you so much for your help


"Bob Phillips" wrote:

Not directly as it reflects a selected value, not a reference to a list
position. I think that you would have to write some VBA to trap the list
change and update the DV knowing what it was and where that was in the
original list.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"clara" wrote in message
...
Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help

How to make a data validated field refresh
 

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



"clara" wrote:

Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help

How to make a data validated field refresh
 

clara,
See my other post for detecting a change in the actual list used for
validation - it's looking for changes in that list, be they additions or
deletes or just changes.

The problem is really to update the displayed selection over in the
validated cell, which after some thought, is what I think you're actually
after. Is it?

If that's the case then we have to add some more code to basically compare
each entry in the validated cells with all entries in the (changed) list and
if the entry in the validated cell(s) doesn't match any entry in the list,
then make an assumption that the value it shows was the one that was changed
and force it to the new entry.

Example: your list contained an item "Joan" and one or more of the
validated cells had that choice chosen, so those show "Joan" in them. Now
you go to your source list and change "Joan" to "Joanne" but the validated
cell still shows "Joan" - since we don't know that "Joanne" was "Joan" before
the change, we have to look at all validated cells, get their value and
compare to all values in the source list, and if it doesn't match (we don't
find "Joan" in the list) we make an assumption that Joan used to be in the
source list and it was changed to Joanne (detected by _Change event) and
change "Joan" to "Joanne" over in the validated cell.

Is that what you're looking for?

"clara" wrote:

HI Bob,

How can I trap the list change event, could you give me some clues?

Clara
--
thank you so much for your help


"Bob Phillips" wrote:

Not directly as it reflects a selected value, not a reference to a list
position. I think that you would have to write some VBA to trap the list
change and update the DV knowing what it was and where that was in the
original list.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"clara" wrote in message
...
Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help

How to make a data validated field refresh
 

Hi JLatham,

Thank you very much. I alway think there is a hidden reference between the
displayed cell and the validation list, but It seems not. So, there is no
hack way to do the change. Anyway, thank you very much for your code.

Clara
--
thank you so much for your help


"JLatham" wrote:

clara,
See my other post for detecting a change in the actual list used for
validation - it's looking for changes in that list, be they additions or
deletes or just changes.

The problem is really to update the displayed selection over in the
validated cell, which after some thought, is what I think you're actually
after. Is it?

If that's the case then we have to add some more code to basically compare
each entry in the validated cells with all entries in the (changed) list and
if the entry in the validated cell(s) doesn't match any entry in the list,
then make an assumption that the value it shows was the one that was changed
and force it to the new entry.

Example: your list contained an item "Joan" and one or more of the
validated cells had that choice chosen, so those show "Joan" in them. Now
you go to your source list and change "Joan" to "Joanne" but the validated
cell still shows "Joan" - since we don't know that "Joanne" was "Joan" before
the change, we have to look at all validated cells, get their value and
compare to all values in the source list, and if it doesn't match (we don't
find "Joan" in the list) we make an assumption that Joan used to be in the
source list and it was changed to Joanne (detected by _Change event) and
change "Joan" to "Joanne" over in the validated cell.

Is that what you're looking for?

"clara" wrote:

HI Bob,

How can I trap the list change event, could you give me some clues?

Clara
--
thank you so much for your help


"Bob Phillips" wrote:

Not directly as it reflects a selected value, not a reference to a list
position. I think that you would have to write some VBA to trap the list
change and update the DV knowing what it was and where that was in the
original list.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"clara" wrote in message
...
Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help

How to make a data validated field refresh
 

clara,
No, there's not a 'linked cell' like there would be for a dropdown list from
one of the toolboxes.

"clara" wrote:

Hi JLatham,

Thank you very much. I alway think there is a hidden reference between the
displayed cell and the validation list, but It seems not. So, there is no
hack way to do the change. Anyway, thank you very much for your code.

Clara
--
thank you so much for your help


"JLatham" wrote:

clara,
See my other post for detecting a change in the actual list used for
validation - it's looking for changes in that list, be they additions or
deletes or just changes.

The problem is really to update the displayed selection over in the
validated cell, which after some thought, is what I think you're actually
after. Is it?

If that's the case then we have to add some more code to basically compare
each entry in the validated cells with all entries in the (changed) list and
if the entry in the validated cell(s) doesn't match any entry in the list,
then make an assumption that the value it shows was the one that was changed
and force it to the new entry.

Example: your list contained an item "Joan" and one or more of the
validated cells had that choice chosen, so those show "Joan" in them. Now
you go to your source list and change "Joan" to "Joanne" but the validated
cell still shows "Joan" - since we don't know that "Joanne" was "Joan" before
the change, we have to look at all validated cells, get their value and
compare to all values in the source list, and if it doesn't match (we don't
find "Joan" in the list) we make an assumption that Joan used to be in the
source list and it was changed to Joanne (detected by _Change event) and
change "Joan" to "Joanne" over in the validated cell.

Is that what you're looking for?

"clara" wrote:

HI Bob,

How can I trap the list change event, could you give me some clues?

Clara
--
thank you so much for your help


"Bob Phillips" wrote:

Not directly as it reflects a selected value, not a reference to a list
position. I think that you would have to write some VBA to trap the list
change and update the DV knowing what it was and where that was in the
original list.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"clara" wrote in message
...
Hi all,

I have changed the list of a data validation, how can I make the
corresponding data validated field refreshed.

Clara
--
thank you so much for your help