Set max characters in cell (Excel 2007)

[BK]

Can you set the maximum number of text characters you want entered into a
cell in Excel 2007? If so, how?

thanks,
BK

 

[Dave Peterson]

Maybe you could use data validation?

Can you set the maximum number of text characters you want entered into a
cell in Excel 2007? If so, how?

thanks,
BK

 

[BK]

THANKS! Data Validation did work, didn't even think to work there.

thanks again,
BK

 

[Gord Dibben]

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

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

No message, no retyping.

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

 

[Dave Peterson]

Good point.

That's why I like to use an adjacent cell formatted in a big red font with a
formula like:
=if(len(a1)<20,"","<-- Too long, please shorten description")

Then I (as the developer) have to worry about which characters to keep (truncate
after 20 or keep the last 20????).