text length

[zerosleep]

Hi,

looking for a line of code to check and make sure that there are no more than 40 characters including blanks within a cell. I know it's something to do with validating data, but can't come up with anything as far as VB codes for it.
or len()......something something - can't quite get it, that why I here. would appreciate any help on this matter.

Thank You,
-zerosleep

[Ron Coderre]

No VBA necessary (if you catch it upon entry):

Data>Validation
Allow: Text Length
Data: less than or Equal To
Maximum: 40


But, if you must check after the fact, here's some sample code:


Option Explicit
Sub Max40()
Dim theCell As Range
For Each theCell In [TestRange]
If Len(theCell.Value) > 40 Then
theCell.Value = Left(theCell.Value, 40)
End If
Next theCell
End Sub

Does that help?

Regards,
Ron

[zerosleep]

Actually the data is usually already entered(sometime a whole lot of it).
and I was looking to just run a macros that would loop thru the selected cell and make sure they're not more than 40 characters long- if they are, color the cell or something indicating it's over the 40 limit. Thank for the suggestion though.

-zerosleep


thanks! just what i was looking for.