Update (refresh) cell after programatically change the data validation

[oteixeira62]

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

[#2]

@oteixeira62 

Do you mean that the users don't see the new data in the drop down list?

[#3]

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.

Octavio

[#4 · Accepted solution]

@oteixeira62 

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

[#5]

Hello Hans,
I posted before reading your last advice. It is quite a good solution. Many thanks for your help.
Octavio