+ Reply to Thread
Results 1 to 21 of 21

Validating number of characters in a cell range

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Validating number of characters in a cell range

    Hello Excel Forum Community,

    I am in need of some assistance making a macro that will loop through columns A2 through N2 to make sure each cell doesn't exceed the max characters.

    To make more sense, I have been using excel cell validation tool to limit a range of cells from having a certain amount of characters. For example in column A, the max length for each cell is 50 characters. If someone typed in anything that exceeds fifty characters then excel prompts an error message. This currently works fine however, when I am copying bulk data from another worksheet and many of the copied data have more then 50 characters then the data validation doesn't not work anymore. This is why I need a macro.

    To further complicate things, I want it to highlight each cell that is yellow and pompt a message when it is finished validating.

    Attached is a sample sheet:

    Max Lengths for columns
    A - 50
    B - 3000
    C -100
    D - 3000
    E - 30
    F - 50
    G - 300
    H - ignore (skip, doesn't matter)
    I - 100
    J - 3000
    K - 30
    L - ignore (skip, doesn't matter)
    M - 20
    N - ignore (skip, doesn't matter)

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Validating number of characters in a cell range

    Hi,

    See the attached which uses conditional formatting on Sheet1(2) to achieve the highlighting.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Hi Rich,

    The problem is that when I copy bulk data into the fields it will highlight them red (I hope), however it will not notify the user or stop the user from running another macro with the worksheet with the mistakes. If I can insert the validating macro within my current macro, it can stop them from executing it and force them to fix the mistakes.

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Validating number of characters in a cell range

    maybe something like
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Ok, this sort of works but it is solely based on the previous row... meaning that if the previous row isn't filled then it will not work.

    This works fine for my needs. Is there a way you can add a message box to this?

    Also, I am looking to add this in with my other macro, if it finds anything and it highlights it yellow can it end the sub stop the macro? and if it doesn't find anything I want it to continue... perhaps something like this

    Please Login or Register  to view this content.
    Last edited by johnph; 08-07-2012 at 02:23 PM.

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Validating number of characters in a cell range

    try
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Nope, now it doesn't do anything at all except the msgbox -_-
    Last edited by Cutter; 08-10-2012 at 07:07 PM. Reason: Removed whole post quote

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Validating number of characters in a cell range

    Johnph, in your example, the code highlights with yellow cell E2, reported about the incident and out of the procedure... Is not this what you intended?

  9. #9
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    It is supposed to highlight all cells if it finds any fields that don't match the array.
    If it find cells it also stops the macro outputting a message telling them to fix all cells marked yellow.

    If there isn't any fields highlighted then it doesn't prompt the message and continues with the "else" statement.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Validating number of characters in a cell range

    try
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    giving this a shot now, sorry for the delay

  12. #12
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Still prompting the message box even though the data validation is correct

  13. #13
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Please Login or Register  to view this content.
    works fine, however the message box and the exit sub when it finds any fields that are too long doesn't any ideas?

  14. #14
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Please Login or Register  to view this content.
    I think that the BU portion is always returning true and that may be the issue.

  15. #15
    Forum Contributor
    Join Date
    03-15-2007
    Location
    AL, USA
    MS-Off Ver
    2007
    Posts
    174

    Re: Validating number of characters in a cell range

    Sorry for the late reply, I don't hang out here often anymore!

    Try setting bu to an integer and checking it that way. And don't forget to end your if statements.

    This code compiles, but I can't run it.

    Please Login or Register  to view this content.
    Last edited by mshale; 08-20-2012 at 04:18 PM.

  16. #16
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Please Login or Register  to view this content.
    Same issue with the Boolean, something is always returning true. Anyone have any suggestions?

  17. #17
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Please Login or Register  to view this content.
    The value of BU changes on this line after debuging

  18. #18
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Validating number of characters in a cell range

    John, can you attach a file with your data where the error occurs?

  19. #19
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    TEST.xlsxOk Nilem, I found a breakthrough here. This code works if create a new worksheet:

    Please Login or Register  to view this content.
    But I think it is a worksheet related issue, is there a way to make this for sheet2?

    Attached is a test file, the macro must work for sheet2 (test2).

  20. #20
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Validating number of characters in a cell range

    maybe so, try
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    06-29-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    98

    Re: Validating number of characters in a cell range

    Hey Nilem and mshale thanks so much for your help. I figured it out, it wasn't working on my specific sheet because one of my columns had 25 letters on an array of 20, since the column was yellow it was hard to identify it from the macro.

    You guys rock thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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