Limit characters in cells with VBA and require action

[mfarus]

I have a column that I need to limit text to 40 characters in each cell. I can't use validation because data will be pasted in. I have the following code that almost works, but I need the message to keep coming up until the problem is fixed in any one cell. Right now, as soon as you enter, tab or click off the cell the message comes up, but you can click ok and then go on to other things without fixing the problem. I need them to fix the problem before moving on to anything else on the worksheet.

Here is what I have so far:

Please Login or Register  to view this content.

[DP978]

Edit: Didn't see you couldnt use validation

[JBeaucaire]

This will prompt for the corrected description in a popup box, it will present the first 40 characters as a default so you could just press ENTER to truncate.

Please Login or Register  to view this content.

If you do not shorten it or enter another value still too long, it will loop until you do.

[JBeaucaire]

You might want to just have the cell value truncated and get a "beep" to indicate it happened.

Please Login or Register  to view this content.

[mfarus]

I can't use your second example as the descriptions need to be modified, not just truncated. I had already tried this and ran into that problem.

Your first example is close, but instead of truncating as explained above, could it just force them back to the cell to correct? Or show them the entire text in the dialoge box and let them modify?

Also, if they just hit cancel if wiped out the text in the cell and put in "FALSE", which isn't good.

[JBeaucaire]

How about this:

Please Login or Register  to view this content.

[mfarus]

That is perfect, you're Awesome. Thanks