+ Reply to Thread
Results 1 to 42 of 42

Complex Character Combination Validation Code Needed

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Arrow Complex Character Combination Validation Code Needed

    Hello again,

    I really need a validation code for Cell B15. I realize that a macro could do this, but a validation code is what I really would need:


    Cell B15 can only allow at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):

    I
    I I
    I I I
    IV
    IA
    I IA
    I I IA
    IVA

    or (some combination examples):
    IA & I I I
    I I & I I IA
    I VA & I IA


    If the user fails to meet these requirements, then he should get an error message telling him to try again.
    Possible? -Juda

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Complex Character Combination Validation Code Needed

    You dont need a formula per se. You only need to use the built-in Data Validation using the list option and using all of the values you show as the list items.

    See this link for help on Data Validation
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Thanks, however, a drop-down list will not be suitable for this worksheet, unfortunately. Is there a way to have the list entered as a code?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Complex Character Combination Validation Code Needed

    By whatever means it gets accomplished, something needs to know all possible combinations of valid entries.

    By "code" do you mean VBA?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Complex Character Combination Validation Code Needed

    Hello Juda,

    Here is validation routine that is placed inside the Worksheet_Change event module. Spaces before and after the "&" character are ignored, but spaces between characters remain untouched. The validation has 2 parts. First it checks that the string only contains the following characters: A, I, V, & and spaces. Second, only the strings you specified will be considered valid. Here is the macro...
    Please Login or Register  to view this content.
    How to Save a Worksheet Event Macro
    1. Copy the macro using CTRL+C keys.
    2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
    3. Left Click on View Code in the pop up menu.
    4. Paste the macro code using CTRL+V
    5. Make any custom changes to the macro if needed at this time.
    6. Save the macro in your Workbook using CTRL+S
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  6. #6
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Thanks to all - however, I'd like the code to be in Validation (Data/Validation/Allow Custom Formula) Can that be done, though? -Juda

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Complex Character Combination Validation Code Needed

    No. That is why Leith provided the VBA code.
    There is no way a formula can predict which combination of characters will be entered. There could be more than two combinations entered and it gets quite messy and complicated.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Complex Character Combination Validation Code Needed

    Hello Juda,

    Given the complexity of the validation required, I doubt this could be done using Data/Validation/Allow Custom Formula. It was difficult enough using advanced VBA code.

  9. #9
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Well, then I could probably make that work if it could be integrated into my worksheet on this thread here. It might be a bit confusing, though... would you mind having a look at that? Thanks again for everything, Juda

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Complex Character Combination Validation Code Needed

    Hello Juda,

    After reading your previous posts, this is very complex set of operations. Relying on worksheet events is to limiting for what you want to do. You really need a UserForm to handle the input, validation, and output processes. In my opinion, your request for help on this is beyond the scope of this forum. Not to say this can't be done, but the time and energy required is considerable. Not many people would be willing to do a project like this, especially for free.

  11. #11
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Complex Character Combination Validation Code Needed

    Quote Originally Posted by Leith Ross View Post
    Hello Juda,

    After reading your previous posts, this is very complex set of operations. Relying on worksheet events is to limiting for what you want to do. You really need a UserForm to handle the input, validation, and output processes.
    I think so, however, Juda should make his requirement less complicate. I mean he should combine between auto- fomulas / validations and manual input with dummy cells.

  12. #12
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Hi again, and thanks for the input - I am still not persuaded that a validation isn't possible though - call me stubborn if you like

    I'm quite sure I've done something like this before, where (in this case) the validation code would check if the data entered meets the requirements - it doesn't matter how long the formula would be, and would work something like this:

    If the value entered equals:
    I
    I I
    I I I
    IV
    IA
    I IA
    I I IA
    IVA
    IA & I I
    I & I IA
    IA & I I I
    I & I I IA
    IA & IV
    I & IVA
    I IA & I I I
    I I & I I IA
    I IA & IV
    I I & IVA
    I I IA & IV
    I I I & IVA

    ...then an error message will not appear - if a value other than one of the combinations above is not entered, then an error message will appear.
    By all means correct me if I'm wrong - but wouldn't a regular 'IF/OR' formula do the trick here? This is the previous code I had used in Validation for this cell to check that there were spaces between the 'I's, only capital 'I's were entered, and no number '1's:

    Please Login or Register  to view this content.
    That makes me believe there's still something I can do for validation. Any more suggestions? -Juda

  13. #13
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Complex Character Combination Validation Code Needed

    If one is set against Validation by VB, one could set list validation to a range of 72 cells and turn off the in cell dropdown, option so the user would have type in one of the acceptable strings.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  14. #14
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    How about my idea of a long validation code?
    Last edited by swordswinger710; 12-16-2009 at 12:29 PM.

  15. #15
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: Complex Character Combination Validation Code Needed

    How about splitting that into two cells, and simply using validation on each cell, combining the results when needed?

    No reason to make something more difficult on purpose imo.

  16. #16
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Thanks, but I'm not sure I got that. I can only use one cell for the data entry...

  17. #17
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: Complex Character Combination Validation Code Needed

    Sorry, I missed that it would be multiple values instead of only two.

    Good luck getting this done in only worksheet functions etc. Even if its possible you would end up with a monsterous function that would be nearly impossible to change.

  18. #18
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    It's got to work - I can't believe that it's impossible. I think my previous code just needs to be altered somewhat... somebody?

  19. #19
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Complex Character Combination Validation Code Needed

    Make a long list of acceptable entries in a column.
    Make that column the source for a Validation list
    Uncheck the In Cell Dropdown checkbox in the Validation dialog.

  20. #20
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: Complex Character Combination Validation Code Needed

    ^

    I guess. If he wants to use VBA to generate a list of permutations for what would be acceptable. Given that its an open ended 2 or more of these 8 items. If 8 can be combined, there would be ~40,000 permutations.

  21. #21
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Complex Character Combination Validation Code Needed

    I read the OP as either one of 8 or any pair of the 8.

    64 pairs (duplicates included) + 8 = 72 possibilities.


    Hmm.. If the 8 base strings are in A1:A8,

    =ISNUMBER(MATCH(X1, A1:A8,0)) will tell if X1 is in the list (TestA)
    =ISNUMBER(MATCH(LEFT(X1,FIND("&",X1)-1), A1:A6,0)) will tell if the stuff before "&" is in the list (TestB)
    =ISNUMBER(MATCH(MID(X1,FIND("&",X1)+1,255), A1:A6,0)) will tell if the stuff after "&" is in the list. (TestC)

    So a formula like =OR(TestA, AND(TestB,TestC)) might work. But the list method would give greater control over the acceptable entries. (e.g. rejecting duplicates of the base strings like "I I & I I")
    Last edited by mikerickson; 12-16-2009 at 07:25 PM.

  22. #22
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: Complex Character Combination Validation Code Needed

    I made the same mistake...

    "two or more of the following values "

  23. #23
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Thanks again for all the input - to make things somewhat easier, this approach (once properly translated into code) would be acceptable (the groups in red should be all I really would need):

    Please Login or Register  to view this content.
    Hope that makes it a bit more approachable... thanks again.

  24. #24
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Complex Character Combination Validation Code Needed

    Juda,
    That would work fine with List style Validation.
    Did you try that?

  25. #25
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    I could try it, but I would rather have a line of code in validation, if it's possible (which I think it is!).

  26. #26
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    So, is a validation code going to be possible, or am I dreaming?

  27. #27
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Complex Character Combination Validation Code Needed

    The best way to use validation to insure that the user enters one of your 20 acceptable values is to use list validation. (Putting the list on a hidden worksheet and using Names will avoid cluttering up your working sheet).

    Yes, its possible to do it with a formula Validation, but its a lousy solution to the problem.

  28. #28
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Well, I would really prefer the 'lousy' solution... if I knew how to go about the code - I'd do it myself, but I'm looking forward to finding out how. Thanks again.

  29. #29
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Ok, I managed to figure out the code I needed:

    Please Login or Register  to view this content.
    However, now I am unable to paste that into the Formula box in Validation, because of the length. Is there something I could change in the code to shorten it? Thanks again, Juda

  30. #30
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: Complex Character Combination Validation Code Needed

    Yeah... that does not actually do what you think it does. All it is doing, is telling you if an "I" is present. Example: "IVIZIGI" = True by that formula.

  31. #31
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Hey, you're right. Well, if it's that difficult, then I think I'll try incorporating that macro Leith Ross put all that effort into - let's see how that works...

  32. #32
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Hi again!

    I think Leith Ross' macro is going to be my solution! I did find a few bugs though:

    The '&' symbol should only be allowed if it has spaces before and after it.

    Currently, someone can enter the wrong value and get away with it. It would be great if the user would get an error message with the option of retrying, and the value in the cell would be highlighted and ready for correction (like a validation code would do).
    Thanks again for all of your input (and your great patience with me in this matter)! -Juda
    Last edited by swordswinger710; 12-22-2009 at 09:58 AM.

  33. #33
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Complex Character Combination Validation Code Needed

    Please Login or Register  to view this content.

  34. #34
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Umm, thanks, but I can't get it to work - is this supposed to replace Leith Ross' macro, or is it just an addition? Either way, it doesn't seem to do anything...

  35. #35
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Complex Character Combination Validation Code Needed

    It replaces Leith's code, and goes in the Sheet module:

    Adding Code to a Sheet module
    1. Copy the code from the post
    2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
    3. Paste the code in the window
    4. Press Alt+Q to close the VBE and return to Excel

  36. #36
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Complex Character Combination Validation Code Needed

    Why not use a function like this in your data validation:
    Please Login or Register  to view this content.
    Note: you will need to define a name that uses this function, and then use that name in the DV dialog since you can't use a UDF directly.
    Everyone who confuses correlation and causation ends up dead.

  37. #37
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Complex Character Combination Validation Code Needed

    Demo file attached, FWIW.
    Attached Files Attached Files

  38. #38
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Complex Character Combination Validation Code Needed

    I like that approach.

  39. #39
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Complex Character Combination Validation Code Needed

    Hey thanks!

    shg, I got yours working, except that it still allows lowercase letters. (iv & i i)

    romperstomper, yours works too, except that it allows the '&' without spaces before and after it. (IV&I I)

  40. #40
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Complex Character Combination Validation Code Needed

    Change this line:
    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.

  41. #41
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Complex Character Combination Validation Code Needed

    ... except that it still allows lowercase letters. (iv & i i)
    Please Login or Register  to view this content.
    But I still prefer R's approach.

  42. #42
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Complex Character Combination Validation Code Needed

    Hello Juda,

    All that was needed in the Regular Expression was to change the spacing allowed before and after the ampersand. It was initially set up so spaces didn't matter. Now, there must a single space before and after. It is subtle change that can be easily missed. It is highlighted in blue. The asterisks have been removed. This qualifier means zero or more occurrences of the previous character are allowed.
    Please Login or Register  to view this content.

+ 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