Modify Data Validation Input Message

[tony]

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

 

[Nigel]

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

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

End With

 

[tony]

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

 

[Nigel]

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

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

 

[tony]

Cheers Nigel,

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

Doh!

 

[Nigel]

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

 

[Nigel]

correction, it only works if the validation parameters are the same.

Try this strategy.

Select entire range to have any validation to some default setting, with the
titles and messages in place, then selectively modify the validation rules,
exclude the titles and messages which you do not want to change.

I think this is the reverse of your approach, I tried it and it appeared to
work OK



--

Regards,
Nigel
(e-mail address removed)

 

[tony]

Many thanks for your suggestions Nigel,

I think the problem could be down to the fact that the spreadsheet has
to be usable by all version of excel not just 2007.

I'm not going to pursue this any further as it'll probably cause
problems to other users of the spreadsheet.

Once again thanks for your help.

Regards

Tony