Forum Discussion
Solved
Update (refresh) cell after programatically change the data validation
Hello to all,
I have the following lines of code that work fine in setting up a new validation type for cell E4.
Range("E4").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO"
End With
The code changes the validation but is not visible to user. Is there a way to force the cell to show the new data validation?
Many thanks for any kind help.
Octavio
That is the expected behavior. Changing the source of the data validation list does not affect the cell value, even if that value is no longer valid. Excel cannot know what the new value should be.
You could do this:
With Range("E4") With .Validation .Delete .Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO" End With .ClearContents End With
Do you mean that the users don't see the new data in the drop down list?
- Hello Hans,
No, they do not see the new list. The cell still shows the value of previous/validation (or selection). Only by clicking the cell the new values get available.
OctavioThat is the expected behavior. Changing the source of the data validation list does not affect the cell value, even if that value is no longer valid. Excel cannot know what the new value should be.
You could do this:
With Range("E4") With .Validation .Delete .Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO" End With .ClearContents End With
