Add a limit of 140 characters to any cell

[Imperial]

I am try to limit any cell(s) to 140 characters when typing and show a value
meaning counter as I type to let me know how many characters I have remain in
that cell. Could anyone help me on how I do this. Thanks

 

[Gord Dibben]

Excel cannot count letters as you type them.

You could do this with Data Validation.

The only problem with Data Validation is that you get an error message and
you have to re-type or edit.

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key

No message, no retyping/editing. If less than 140 no action. If greater
than 140, code cuts length to 140.

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) > 140 Then
.Value = Left(.Value, 140)
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