Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Customized Validation (2000)

    Hi,

    Can anyone suggest a method or a macro thorugh which I could customize my validation. The validation that I am looking for is:
    999-xyz/xyz-n

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customized Validation (2000)

    Could you elaborate a bit? I fail to understand what you need.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customized Validation (2000)

    Hi,

    I would like that whenever a user enters data in a particular column it should follow the format :
    999-xyz/xyz-n , meaning first three characters should be numbers, next character should be a "-" again the next three characters should be text followed by "/" followed by three characters text followed by "-" followed by "n" (as "n" is fixed)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customized Validation (2000)

    The attached woirkbook contains a custom function MyValidate in a module. This function takes a value and returns TRUE if the value conforms to the pattern 999-xyz/xyz-n, FALSE otherwise.
    In the worksheet, we want to limit entries in A1:A26. Cell B1 contains the formula =MyValidate(A1), and this is filled down to B26. With cells A12:A26 selected, data validation has been set to custom, with formula =B1 (this is propagated to the other cells automatically).
    Note: I have left column B visible, so that you can see the formulas. If you like, you can hide column B.

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customized Validation (2000)

    Hi Hans

    Thanks for the reply. It worked. Can it be done like validation function in excel meaning restricting user not to enter any incorrect value and giving an error alert.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customized Validation (2000)

    I'm sorry, I don't understand. The workbook I attached to my previous reply has validation set for the cells A1 to A26. If you try to enter a value that does not fit the pattern, you'll get an error message. See screenshot.

    To see how the validation is set up, select A1:A26, then select Data | Validation...

  7. #7
    New Lounger
    Join Date
    Jun 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customized Validation (2000)

    Sorry, missed it before. Understood it now. It works beautifully. Thanks a lot

Posting Permissions

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