maximum characters in cell

[Brad Sayers]

Is there a way to receive an alert if I type more than 'x' characters in a
cell?

I have tried data, validation and played with some options but it's not
really what I want.

For example, I don't want more than 40 characters in a cell as I upload this
data to an application that will truncate anything beyond this number (for
example). However, I need to paste data into these cells and would like to
be notified If I go over the limit. One of the options in data, validation
will wrap the text in a cell if more than 40 to fit the column, but this is
not what I want.

Is there any way I can do this? TIA, Brad

 

[Frank Kabel]

Hi
if you use 'Data - Validation - Text' you can define the max. length

 

[Brad Sayers]

Thanks Frank,

I choose that but it still allows me to paste text in that is longer than
the set amount. What I have is: allow text length, less than or equal to,
40 but it will allow me to paste in much more.

Brad

 

[JE McGimpsey]

You could use an event macro:

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nMAX As Long = 40
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Range("A1")
If Len(.Text) > nMAX Then
MsgBox "The text in cell " & _
.Address(False, False) & " is too long (" & _
Len(.Text) & " characters)" & vbNewLine & _
"Max. Characters: " & nMAX
.Activate
End If
End With
End If
End Sub

 

[Gord Dibben]

Brad

Unfortunately DV ignores pasted text length.

J.E.'s event code is the way to go.

Gord Dibben Excel MVP

 

[Brad Sayers]

JE, thanks for the code - works great. I should have said 40 characters in
each cell per a particular column. I have played around a bit to define the
range for a column but I am not getting the syntax right. What do I need to
use to define per column? Thanks Again! Brad

 

[JE McGimpsey]

one way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nMAX As Long = 40
Dim rCell As Range
Set Target = Intersect(Target, Columns(1))
If Not Target Is Nothing Then
For Each rCell In Target
With rCell
If Len(.Text) > nMAX Then
MsgBox "The text in cell " & _
.Address(False, False) & " is too long (" & _
Len(.Text) & " characters)" & vbNewLine & _
"Max. Characters: " & nMAX
End If
End With
Next rCell
End If
End Sub

Note that I took the .Activate command out, since there could be
multiple cells within the column that exceeded the maximum.

If I were doing this, I might for a correction for each cell found to
exceed the max characters. I'd probably use a userform, but an inputbox
might do in a pinch:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nMAX As Long = 40
Const sTOO_LONG As String = _
"The text in cell $$ is too long (%% characters)" & _
vbNewLine & "Max Characters: ##"
Dim vText As Variant
Dim rCell As Range
Set Target = Intersect(Target, Columns(1))
If Not Target Is Nothing Then
For Each rCell In Target
vText = rCell.Text
If Len(vText) > nMAX Then
With Application
Do
vText = .InputBox( _
Prompt:=.Substitute(.Substitute( _
.Substitute(sTOO_LONG, "%%", Len(vText)), _
"$$", rCell.Address(False, False)), _
"##", nMAX), _
Default:=Left(vText, nMAX), _
Title:="Too Long!", _
Type:=2)
If vText = False Then Exit Sub 'User cancelled
Loop Until Len(vText) <= nMAX
.EnableEvents = False
rCell.Value = vText
.EnableEvents = True
End With
End If
Next rCell
End If
End Sub

 

[Brad Sayers]

JE, great work. Just what I needed. I appreciate your expertise and
willingness to help me out in this. Have a great day - Brad