Text Length Data Validation, without error message.

[Guest]

A user would like to have cells limited to a certain length of input, but not
have an error message displayed if the length is exceeded... just keep the
text up to the limit, and prevent any further entry.

With Data Validation, if you uncheck the checkbox which tells it to show the
error message, it stops preventing the entry... does not do the checking for
the limit.

Is there an easy way to limit a cell to 100 characters, say, that when
someone exceeds that limit, the entry is just truncated to 100 without any
error message to click off?

One way that I thought of was some programming with Intersect, but at least
with the worksheet change event, the Activecell is the current cell, which
isn't necessarily where the user made the change, but where they clicked.

Thanks.
Mark

 

[Harald Staff]

Hi Mark

You need a macro to change cell content. Try this, it truncates all B column
entries exceeding 100:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Column = 2 Then 'B column
If Cel.HasFormula = False Then
If Len(Cel.Value) > 100 Then
Cel.Value = Left$(Cel.Value, 100)
End If
End If
End If
Next
End Sub

HTH. Best wishes Harald

 

[Ivan Raiminius]

Hi Harald,

I would propose a little change to your code, just to improve
performance:

Private Sub Worksheet_Change(ByVal Target As Range)
dim Cel as range
set target = intersect(target, range("b:b")
if target is nothing then
exit sub
end if
set target = target.SpecialCells(xlCellTypeConstants, 3)
if target is nothing then
exit sub
end if
For Each Cel In Target
Cel.Value = Left(Cel.Value, 100)
Next
End Sub

works also for column B

Regards,
Ivan

 

[Harald Staff]

Probably a good idea, Ivan. It sounded like a "type here" worksheet where
paste operations are small, few and rare, so I didn't care about them. One
fine day I'll learn not to listen to myself ... <g>

Best wishes Harald

 

[Guest]

Thanks, guys.

I thought I posted a 'thanks' note, but if I did, it's not here now... guess
I forgot.

Your suggestions are the type of thing I had in mind, and I'm working on
implementing them now.

Personally, I think the standard Excel validation with the error message for
exceeding the character limit ought to be what they use, but...

See ya.