Excel VBA Replace Data Validation Titles and Input Messages

[dtodorov]

Hello, I am somewhat new to Excel VBA so please pardon my ignorance.

I am trying to create a VBA script that iterates through all the cells in a worksheet, finds those that have data validation enabled, and replaces ONLY the text fields leaving all validation formulas, etc fields unchanged. The reason why I am doing this is because I am translating data validation title/message fields into another language but do not want to change validation settings as such.

The data validation text fields that I am modifying are:
Dim target as Range
Set target = Worksheets(Left(strAddress, InStr(strAddress, "!") - 1)).Range(Right(strAddress, Len(strAddress) - InStrRev(strAddress, "!")))
target.Validation.ErrorMessage = strErrorMessage
target.Validation.ErrorTitle = strErrorTitle
target.Validation.InputMessage = strInputMessage
target.Validation.InputTitle = strInputTitle

For some reason, if I execute the above I am getting application error 1004; if I replace the variables with a specific string (such as "TEST"), there is no error, and the text fields are set correctly... I am sure I am doing something wrong.

I'd appreciate any thoughts/comments. Thanks.

 

[BDeitrick]

I'm not sure what you are doing, but this worked fine for me:
Sub TestMacro()
Dim strAddress As String
Dim target As Range
Dim myC As Range
Dim strErrorMessage As String
Dim strErrorTitle As String
Dim strInputMessage As String
Dim strInputTitle As String
strAddress = "Sheet1!A1:H10"
strErrorMessage = "Test 2"
strErrorTitle = "Test 3"
strInputMessage = "Test 4"
strInputTitle = "Test 5"

Set target = Range(strAddress).SpecialCells(xlCellTypeAllValidation)
For Each myC In target
myC.Validation.ErrorMessage = strErrorMessage
myC.Validation.ErrorTitle = strErrorTitle
myC.Validation.InputMessage = strInputMessage
myC.Validation.InputTitle = strInputTitle
Next myC
End Sub

There is no need to split out the sheet name and cell address the way that you did - as long as strAddress is a valid sheet and range....
Bernie

 

[dtodorov]

Bernie,

You are absolutely right - it does work... After hours of troubleshooting I've concluded that the problem is somewhere between my chair and my keyboard; I had been trying to set the InputTitle/ErrorTitle attributes to a string longer than 32 characters, hence the 1004 error.

Many thanks for taking the time to analyse and reply though! Had you not confirmed that it works, I would have still be changing my VBA code, rather than looking into logical errors.

Dob