Yes, it does that automatically every time the cell value is changed
with the WorkSheet Event code in Private Sub Worksheet_SelectionChange(ByVal
Target As Range). You must understand that this does not do real time
checking of the text as you are typing. I am not sure that can be done. But,
as soon as you hit Enter or click to another cell, E10 will be re-evaluated
by the code. The messagebox attempts to give you a line number where the
Alt/Enter is needed. Of course the number of lines you see displayed depends
entirely upon how wide column E is. What the Line number refers to is which
carriage return has more than 255 characters before it. So it is really
counting carriage returns, which is what you requested. The Line number is
something I put in extra to help you know which carriage return has too many
characters before it.
If you type 150 characters and Alt/Enter, then type 350 characters and
Enter, here is what the code determines:
Starting at character 1 it finds the position of the next Carriage return at
151. Determines the length is OK and loops through the text again starting
at character 152. It does not find a 2nd carriage return. It then subtracts
the position of the last carriage return (151) from the entire number of
characters in E10 (500) and if that is greater than 255, which it is (349),
then the message will direct you to Line 2.

By the way, I found a flaw in my code that goes in the general module, so
here is the updated procedu

Sub CountCharacters()
Dim SearchString, SearchChar, CurrentPos
Dim LastPos, SearchPos, LineLength, Counter
SearchString = Range("E10").Value ' String to search in.
SearchChar = Chr(10) ' String to search for
SearchPos = 1
LastPos = 0
Counter = 1
CurrentPos = InStr(SearchPos, SearchString, SearchChar, 1)
If CurrentPos = 0 And Len(SearchString) 255 Then GoTo 1
Do Until CurrentPos = 0
CurrentPos = InStr(SearchPos, SearchString, SearchChar, 1)
LineLength = CurrentPos - LastPos
If CurrentPos = 0 And Len(SearchString) - LastPos 255 Then GoTo 1
If LineLength 255 Then
1:
Range("E10").Select
MsgBox ("Please Go Back to E10 and put an ALT/ENTER in Line " & Counter)
Exit Sub
End If
SearchPos = CurrentPos + 1
LastPos = CurrentPos
Counter = Counter + 1
Loop
End Sub

Mike F

"Mikeice" wrote in
message ...

Thanks so much Mike but is it possible that after the first message
appears can it then do the count again if the cell E10 get more text
again typed into it?


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393533