Setting a Length Limit on Cells

@Tomek: Agreed.
I didn't re-read the article before my last post and had forgotten that he didn't want to use the Data Validation tool. No matter; the more detailed explanation of how to do it using DV may help someone.

It puzzles me why the person asking for help wants to make things complicated and not use data validation. I understand that some people do not want or cannot use macros, but validation is a standard functionality of Excel. Not using it would be like refusing to format cells to display the cell content in the best way.

Another possible solution:
Data > Data Tools > Data Validation > Settings tab > Allow:
then in the dropdown list select "Text length", select the appropriate condition and length (in this case "less than", and "15" in the "Maximum" field, or refer to a cell containing the appropriate value).
Now in the "Error Alert" tab, enter an appropriate heading and warning text. It may be advisable to use the "Input Message" tab to inform the user as to what is required before he enters anything

Interesting approach, Brian. Would you mind posting the code of your text fitting subroutine?

Another approach - albeit more complicated is to reduce the font size until the text fits into a particular width cell. It's easier to use a helper cell and requires a macro to run a loop which having got the width of the text cell, sets the width of the helper cell, decreases the font size, reads the autofit width of the helper cell until the width is less or equal to the text cell width. Then the macro simply has to apply that font size to the initial cell. It makes sense to limit to the loop to (say) 6 point font or the text becomes unreadable.

I use this method all over worksheets to display filenames, paths and object names. It means I can see all the data and it looks quite posh!

For those who wish to avoid the helper column- based and macro-based solutions, the following solution, which uses conditional formatting, may be apposite:
Create a new conditional format rule for the first relevant cell (say, C1) which uses the following formula to determine which cells to format:
=LEN(C1)>15
and, after clicking the "Format..." button, choose "Custom", at the bottom of the list in the "Number" tab, and paste the following string into the box just under the "Type: "heading:

;;;"Text too long: "@

If so desired, set the font colour under the "Font" tab and/or the fill colour under the "Fill" tab, then keep on OK-ing until you are done. Test by putting a string with more than 15 characters into cell C1; it should appear preceded by "Text too long: " and be displayed in any colouring you set. Texts less than 16 characters long should appear normally. If it works as expected, copy cell C1 and "Paste Formatting" into all relevant cells.

Willy, it seems that your approach assumes the event is triggered by having the same value pasted into multiple cells, but that's just one scenario. If the values differ then a loop must be used.

If you enter the same value in more than one cell a the time by pressing Ctrl+Enter, the second macro has an annoying behavior of displaying the message box for each of the cells because a For Each loop is used, which has no sense in this case. When you enter something to long in several cells at the time, Excel enters the full entry in all cells. Then Excel runs the event macro Worksheet_ChangeWorksheet_Change. My version of the macro hereafter checks if the first value in the range is to long and if so it truncates the whole range in one go:

Private Sub Worksheet_Change(ByVal Target As Range)
Const iChars As Integer = 15 'change as disired
Dim rng As Range
Set rng = Intersect(Target, Range("A1:A10")) 'change range as disired
If rng Is Nothing Then Exit Sub
If Len(rng(1)) <= iChars Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
rng = Left(rng(1).Value, iChars)
MsgBox "Range " & rng.Address & " has more than " & iChars _
& " characters." & vbCrLf & "It has been truncated."
ExitHandler:
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

I removed Set rCell = Nothing and Set rng = Nothing because after that the macro finishes and the memory they might use is then freed anyway.