Data Validation Input Message change

[ChrisBurns]

Hi all,

I have a worksheet that has about a dozen validation input messages on.....
I have this code for the message i want to include

Sub Add_Cell_Input_Message_Cell()

With Range("HREndCurrentProjectStage_ForecastDate").Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "Date Entry"
.InputMessage = "You are only required to enter a forecast date if different from the Baseline.... DO NOT ENTER TBC"

End With

End Sub

however there is an existing our of date message already in place that i need to replace

I'd rather not use 'remove all validation' as there are other areas that need no change...

I need to figure out a way to firstly clear the existing messages or replace them with new

Thanks

 

[Joe4]

Is there any rhyme or reason as to which ranges have which messages?
If you can identify just the range you need to change, select just that range, and then you should be fine.

Or, if you know the message you want to replace, you could use code like this to just replace a specific message with a new message:

    With Range("HREndCurrentProjectStage_ForecastDate").Validation
        If .InputMessage = "A specific message" Then
            .InputMessage = "Enter some other message"
        End If
    End With

 

[ChrisBurns]

Thanks Joe,

This didn't work either.....

The original Validation Message is "Please enter dates as dd/mm/yy"
Some of our team are entering TBC which also starts throwing errors on the main tracker that the 80ish workbooks feed into
I will probably force dd/mm/yy as the only valid entry in these cells

It would be nice to also change the message though.....

 

[Joe4]

I tested it out on a one cell range. You probably will need to loop through your range to check each cell in that range individually.
If you have problems figuring that out, post back, and I'll see if I can help you with that.

 

[ChrisBurns]

Thanks Joe,

This didn't work either.....

The original Validation Message is "Please enter dates as dd/mm/yy"
Some of our team are entering TBC which also starts throwing errors on the main tracker that the 80ish workbooks feed into
I will probably force dd/mm/yy as the only valid entry in these cells

It would be nice to also change the message though.....

It appears that this doesn't work due to the message title also being populated with Date Entry...... how would i code this?

 

[Joe4]

Do you mean something like this?

This loops through your entire named range, and wherever it find validation with an input message of "Please enter dates as dd/mm/yy", it will change both the message and title of that Data Validation for that cell.

    Dim cell As Range
    
'   Loop through each cell in named range
    For Each cell In Range("HREndCurrentProjectStage_ForecastDate")
'       If particular message is found, change title and input message
        If cell.Validation.InputMessage = "Please enter dates as dd/mm/yy" Then
            cell.Validation.InputTitle = "Some Other Title"
            cell.Validation.InputMessage = "Enter some other message"
        End If
    Next cell