I think I would drop Data|Validation and use a worksheet event instead.

If you want to try it, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim maxLen As Long
Dim TruncatedText As String
maxLen = 1100

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("b9")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If Len(Target.Value) maxLen Then
TruncatedText = Mid(Target.Value, maxLen + 1)
MsgBox "Cell: " & Target.Address(0, 0) & " has been truncated!" _
& vbLf & TruncatedText & vbLf & "was chopped!"
Application.EnableEvents = False
Target.Value = Left(Target.Value, maxLen)
'or
'target.value = ""
End If

errHandler:
Application.EnableEvents = True

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

==============
You may want to teach your users that by adding alt-enters every 80 to 100
characters, then way more than 1100 characters will be able to be seen in that
cell. You may have to make the font smaller when you get really long strings.



tkaplan wrote:

I have a merged range of cells B9:I9. I have a cell named TextLength
with the formula Len(B9) and a cell named TooLong with the formula
MID(B9,1100,50).
Here's what I am trying to do:
When the user updates the cell, if he goes past 1150 characters (the
cell will not display past that) i would like a message box to pop up
telling them:
"The cell has reached its maximum limit. Please delete everything after
" & TooLong & " and go to the next row".

I would like the text of TooLong to be either in a different color, or
bold but if that's not possilbe then in quotes.
The purpose of this is because the user won't realize why the entire
text is not displayed in the cell, only formula bar, so this way it
will tell them that they went over and at what point they went over.
I know that i can do validation for the length but i want to include
the text that hit the limit.

Any help would be appreciated.

Thank you.

--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=483601

--

Dave Peterson