Hi,

I'm a bit stuck with the above, I can use the Modify method to change
the data validation criteria for a given range as in the code below:

With Selection.Validation
.Modify Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="=$S$3:$S$4"
.ErrorTitle = "Invalid Entry"
.ErrorMessage = "Enter Yes or No."
End With

However try as I might I cannot change the the actual title and
message displayed when the cell is entered. I need to keep the
validation criteria exactly as set and I think this is where I'm
coming unstuck. I have tried the following code but this just gives me
an error.

With Selection.Validation
.Modify
.InputTitle = myTitle
.InputMessage = myMessage
End With

Any help gratefully received.

Thanks

I think you need to delete the validation first then reset it.

With Range("B3").Validation
.Delete
.Add ..........etc

End With

--

Regards,
Nigel




"tony" wrote in message
...
Hi,

I'm a bit stuck with the above, I can use the Modify method to change
the data validation criteria for a given range as in the code below:

With Selection.Validation
.Modify Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="=$S$3:$S$4"
.ErrorTitle = "Invalid Entry"
.ErrorMessage = "Enter Yes or No."
End With

However try as I might I cannot change the the actual title and
message displayed when the cell is entered. I need to keep the
validation criteria exactly as set and I think this is where I'm
coming unstuck. I have tried the following code but this just gives me
an error.

With Selection.Validation
.Modify
.InputTitle = myTitle
.InputMessage = myMessage
End With

Any help gratefully received.

Thanks

Thanks Nigel,

I've done that but in doing so I have to re-write the validation
criteria to all the cells.

The thing is the InputText and InputMessage are the same for the whole
row, it's just the validation criteria that changes by column.

In some cases I may have 19 columns and upto 8000 rows to write so you
can understand that a change of the Input criteria would be a far
simpler option.

Thanks

Tony

With a bit of experimenting, it seems this works......

With Selection.Validation
.InputTitle = myTitle
.InputMessage = myMessage
End With

--

Regards,
Nigel




"tony" wrote in message
...
Thanks Nigel,

I've done that but in doing so I have to re-write the validation
criteria to all the cells.

The thing is the InputText and InputMessage are the same for the whole
row, it's just the validation criteria that changes by column.

In some cases I may have 19 columns and upto 8000 rows to write so you
can understand that a change of the Input criteria would be a far
simpler option.

Thanks

Tony

Cheers Nigel,

No joy there either, I get the run time error 1004 - Application-
defined or Object-defined error.

Doh!

Well it worked for me (xl2007). Since you are using Selection, have you
selected a range with Validation allocated? Try fully referencing the
range.


--

Regards,
Nigel




"tony" wrote in message
...
Cheers Nigel,

No joy there either, I get the run time error 1004 - Application-
defined or Object-defined error.

Doh!