The only problem with Data Validation is that you get an error message if
you exceed the 50 characters and you have to edit to get back to 50 or less.
You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key
No message, no editing.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) > 50 Then
.Value = Left(.Value, 50)
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is sheet event code. Right-click on the sheet tab and "View Code"
Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.
Gord Dibben MS Excel MVP
On Thu, 5 Nov 2009 07:34:06 -0800, Irene Threadgold <Irene