Message Box: Cell Exceeds 1000 Characters

[typewriterguy]

Would someone be able to provide VBA guidance/correction for this, for Excel 2003?

I would like a message box to pop-up when a user enters > 1000 characters into any Excel cell. The message box should only have an ok button (no cancel or retry) and also include the custom text that is shown below.

"Your description in <FIELDNAME>exceeds Excel's character limit. Please either shorten your description or insert a new row."

I tried the below and was getting a run-time error. Thanks.

---
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D10").Length > "1000" Then
MsgBox "Your description in <FIELDNAME>exceeds Excel's character limit. Please either shorten your description or insert a new row"
End If
End Sub
---

 

[Tom Urtis]

You really don't need VBA for this, just use data validation,

Allow Text Length

data Less Than

Maximum 1000

then set the message alerts as you wish.

 

[typewriterguy]

Thanks the quick response. While what I'm looking for is a validation feature, I tried validation and found that each of the resulting message boxes provided an opportunity for the user to lose their entered data by clicking the cancel or retry buttons. I want a message box that only returns an Ok button and custom text.

 

[Tom Urtis]

And so if the user tries to enter, say, 1005 characters, do you want the message to appear and then after they click OK, have the cell display

either

the first 1000 characters

or

the last 1000 characters

or

no characters (cell gets cleared)

or

some other text such as "typwriterguy rules"


Inquiring minds want to know.

 

[typewriterguy]

If the user enters a value that exceeds 1000 characters, say 1005, then I want the 1005 characters to be stored and for a message box (with ok button) to appear with the custom text and the user clicks ok. The user would decide whether to shorten the character length - not Excel.

 

[Tom Urtis]

Well alright, but how will you enforce the non-action of a user to shorten the text after they click OK? Once that OK is clicked, they can move on to the next cell, or just enter even more text into D10 and click OK again, since that message box will have no teeth to it based on what you say you want.

Put this in your worksheet module, but please also note, I omitted (and of course you can reinstate it if you want) your message box text about Excel having a limit of 1000 characters per cell, that is factually not correct.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "$D$10" Or .Cells.Count > 1 Then Exit Sub
If Len(.Value) > 1000 Then
MsgBox _
"Only 1000 characters are allowed in cell " & .Address(0, 0) & "." & vbCrLf & _
"You entered " & Len(.Value) & " characters." & vbCrLf & vbCrLf & _
"Please click OK and either shorten your description," & vbCrLf & _
"or insert a new row.", 48, "Character limit is exceeded."
End If
End With
End Sub