+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    09-18-2014
    Location
    London, England
    MS-Off Ver
    365 ProPlus
    Posts
    20

    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.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

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

    Here is what you can do with couple of formulas and Conditional Formatting
    Click on cell A1 and choose Use formula to determine which cells to format and enter formula =LEN(B1)<=255 use format and select Fill (Green)
    Then create a new rule and use formula =LEN(B1)>255 use format and select Fill (Red)

    In cell A1 enter formula:

    =IF(LEN(B1)>255,"You have entered more than 255 characters","Number of characters entered: "&LEN(B1))

    Please see attached file.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    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?
    Remember what the dormouse said
    Feed your head

  4. #4
    Registered User
    Join Date
    09-18-2014
    Location
    London, England
    MS-Off Ver
    365 ProPlus
    Posts
    20

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

    Quote Originally Posted by romperstomper View Post
    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!

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    Here's a simple CF example.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-18-2014
    Location
    London, England
    MS-Off Ver
    365 ProPlus
    Posts
    20

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

    Quote Originally Posted by romperstomper View Post
    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!

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    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!"

  8. #8
    Registered User
    Join Date
    09-18-2014
    Location
    London, England
    MS-Off Ver
    365 ProPlus
    Posts
    20

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

    Quote Originally Posted by romperstomper View Post
    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!

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    Just change the text in the number format section in the CF Format dialog.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create new cell with character count
    By kny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2013, 06:44 PM
  2. Replace not working when character length exceeded
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2013, 02:48 PM
  3. Highlighting Conditional Character Count
    By bjcowen9000 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-19-2013, 10:06 AM
  4. [SOLVED] Conditional formatting of a cell in a column if character count reaches a limit
    By Pavan Renjal in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 01-13-2013, 03:16 PM
  5. Conditional Formatting & Character Count Comparison Macro
    By BluTalon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2008, 02:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1