Create pop up error when character count exceeded with Conditional Formatting.

Hi,

So I'm trying to create a pop up error message, or any kind of error message when a character count is exceeded within a cell.

It can't be a Data Validation solution because, as far as I'm aware, copying and pasting into the cell removes the Validation, and this will be happening in the form I'm creating a lot!

All I can come up with is =len(a1)>255 but this only allows for highlighting or strike through... I need an error message to say "character count must not exceed 255."

Hopefully you guys can help?! If you do provide a solution, It would be extra awesome if you could explain why you chose that function, so I can begin to use it again with some understanding and not have to pester all you lovely people again!

Many thanks in advance!

Si.

Originally Posted by romperstomper

With conditional formatting all you could do is make the cell display an error message instead of its contents, but the contents would still be there. I think that would confuse people, so I'd suggest using code instead, if that's an option?

I think having an error message replace the contents would be fine for me, as I would have to apply the formatting to an entire column and that would do to highlight the error and explain the error. I would not want to clutter the sheet by having cells reliant on formulas in cells beside them.

This form I'm creating would potentially be sent to another person to work on while I'm away, and I'd like to make it as simple as possible. However, I'm happy to try a bit of code, providing it was basic stuff.

Thanks for your replies, guys!

Originally Posted by romperstomper

Here's a simple CF example.

Thanks RomperStomper, that's exactly what I was looking for.

I'm being a bit dim though and I'm just trying to copy and paste the formula =REPT("A",255) in to another cell and/or column ( and changing the letter) then typing in 256+ characters, but it doesn't seem to be working.
It just shows up loads of whatever characters I'm inputting into the formula.

Is there something extra I need to include? Also, all the A's in A3, is that a necessary result of the formula. Can the formula be applied to a column without any indication there's a forumla there? I want to apply this formula to a full column with a title in the top column and that's all.

Thanks!

Originally Posted by romperstomper

The formula is only there to demonstrate the conditional formatting - I'm too lazy to type 256 As.

Ignore the formula and look at the custom number format in the Conditional Formatting applied:
;;;"Text is too long!"

Thanks so much! That's perfect and you're help is restoring my sanity... may I ask one last question?

I've been able to transfer the formatting, and apply it to different columns and it looks great! However, how do I edit the custom name, or the message that appears so that I can change the character count that pops up on the error message.

I need to be able to change this quite often to apply it to different columns.

Thanks a million!