Cell Formats - Limit # of characters

[KKD]

I want to limit the # of characters allowed in a cell to 11, regardless of
what the user types in (less characters is fine). How can I do this?

 

[Paul]

Use Data Validation, and allow Text Length -> Less than or equal to -
11. You can add information on the Error Alert tab to notify/preven
users from entering longer data.

 

[Gord Dibben]

Do you want a message telling user he has exceeded the 11 characters and
make him do it over?

Use Data Validation>Text Length.

You can truncate automatically to 11 or less using event code.

No messages or do-overs.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oval As String
On Error GoTo ws_exit:
Application.EnableEvents = False
oval = Target.Value
If oval = "" Then Exit Sub
If Not Intersect(Target, Columns("A")) Is Nothing Then
With Target
If Len(oval) > 11 Then
.Value = Left(oval, 11)
End If
End With
End If
ws_exit:
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[KKD]

Can I copy data into a new cell and force it to drop off any additional
characters beyond 11? Some form of truncating?

 

[Fred Smith]

Certainly:
=left(a1,11)

Regards,
Fred

Can I copy data into a new cell and force it to drop off any additional
characters beyond 11? Some form of truncating?

 

[Paul]

You can use a worksheet_change event to see if a certain cell or rang
of cells changed.


Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ce As Range
If Not Intersect(Target, Range("A10")) Is Nothing Then
For Each ce In Target.Cells
ce.Value = Left(ce.Value, 11)
Next ce
End If
End Sub
--------------------



If you try to paste data (from one or more cells) into a range tha
touches A10, all cells being pasted will be truncated to 11 characters.
This code could be amended to only act upon cell A10, of course, an
could be changed to look at any other cell/range.