Can you use variables in a data validation error alert message?

[evaprotrans]

I have cells with character limits that use data validation. I would like the error alert message to say something like "You have entered X characters; only 200 are allowed", to give the user an idea how much they need to shorten their text. Is it possible to insert a variable (current length of the cell) into the error message?

Thanks,
Eva

 

[CMAnslow]

I dont think it is possible to use Excel data validation for that. You could do it through the use of VBA and a worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target = Range("H5") Then
        Call CheckLength(Target, 25)
    End If
End Sub


Function CheckLength(rng As Range, maxLength As Integer) As Boolean
    Dim val As String: val = rng.Value
    Dim valLength As Integer: valLength = Len(val)
    If valLength > maxLength Then
        MsgBox "You have entered " & valLength & " characters. " & vbCrLf & vbCrLf & "Only " & maxLength & " are allowed.", vbCritical
    End If
End Function

 

[6StringJazzer]

I know this is an old thread but I was looking for a similar issue and noticed an error in this code. The code as written will compare the value of Target to the value in H5; it doesn't determine if Target is the same cell as H5.

    If Target.Address = Range("H5").Address Then

Even better, this version deals correctly with the situation when more than one cell is changed at a time:

    If Not Intersect(Target, Range("H5") Is Nothing Then