Limit text input in Excel 2003

[Agent37]

I have seen a lot of posts talking about how to "limit" the text input
of a cell to a certain number of characters, but using the data
validation option does not stop the user from entering more than a
defined number, it just warns that it is invalid.

What can I do to make typing more than 36 characters impossible? I
just want the input to stop after 36, and if data is pasted into the
cell, I would want it cut off at 36.

Thanks for any help you can give.

-Erich

 

[Frank Kabel]

Hi
AFAIK this is not possible in a cell direct as a macro can't run while
you're in Edit mode

 

[KL]

Data validation option DOES stop the user from entering more than a defined
number and it DOESN'T.

It DOES if two conditions concur:
1. within the Validation dialog window you go to tab 'Error Alert', check
'Show error alert after...' box and select Stop from the 'Style' dropdown.
2. the user manually enters the value or a formula returning that value into
each individual cell.

It DOESN'T if the user copy/paste the value into the cell, copies values or
formulas by dragging the little + in the bottom-right corner of the cell, or
enters the value via VBA.

To ensure there are not more than 36 characters in a cell you could use a
combination of the Validation option and the below code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRng As Range
Set MyRng = Range("A1")
If MyRng.Characters.Count > 36 Then
MyRng = Left(MyRng, 36)
MsgBox "Your text is too long and" & Chr(13) _
& "will be cut to 36 charachters."
End If
End Sub


KL