+ Reply to Thread
Results 1 to 10 of 10

validation rules not working when someone copy paste data on validation cell

  1. #1
    Registered User
    Join Date
    05-06-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question validation rules not working when someone copy paste data on validation cell

    validation rules which i have created are not working when someone copy paste data in the cell in which i have written validation rules want to know is there any way out

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: validation rules not working when someone copy paste data on validation cell

    hello jthakrar,

    welcome to the forum.

    Data validation in a cell only applies if data is entered manually into the cell. Any cell with data validation can always be circumvented by a paste operation. If you want to really foolproof the possible data entries, you'll need a VBA macro solution.

    If you can post some details about the validation requirements, I'm sure someone here will come up with the required code.

    cheers
    Last edited by teylyn; 05-06-2010 at 05:13 AM.

  3. #3
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: validation rules not working when someone copy paste data on validation cell

    the code I posted was for another thread, I apologize.
    ***************************************
    Last edited by antoka05; 05-06-2010 at 06:27 AM.

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: validation rules not working when someone copy paste data on validation cell

    Antonio,

    this is a fine macro, but I'm not sure that this thread is at a point where such a detailed suggestion is suitable. We have not even learned what kind of validation is required, nor for which cells. Also, a macro solution would probably need to be based on a Worksheet_Change event rather than a standard module sub to ensure that no invalid data can be entered into a given range of cells.

    This requires more information from jthakrar before any valid suggestion can be made.

    cheers

  5. #5
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: validation rules not working when someone copy paste data on validation cell

    Yes, you are right, I appreciate your suggestions.

    I'm sorry, the code I posted was for another thread, I apologize for my error!

    Regards,
    Antonio
    Last edited by antoka05; 05-06-2010 at 05:53 AM.

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: validation rules not working when someone copy paste data on validation cell

    the code I posted was for another thread,
    ... the pitfalls of tabbed browsing.

    It happens. Thanks for clearing it up.

  7. #7
    Registered User
    Join Date
    05-06-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: validation rules not working when someone copy paste data on validation cell

    i am sending the excel file in which validation rules are there my validation rules are on cell D11-D15
    E11-E15
    F11-F15

    in the excel file worksheet Payment Form
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-06-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: validation rules not working when someone copy paste data on validation cell

    hi i am sending you attachment of the file where i have put validation rules my validation rules are in D11:E15 of the worksheet Payment Request Form can u guide me how to make sure that this validation rules work in copy paste scenerio also
    Attached Files Attached Files

  9. #9
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,866

    Smile Re: validation rules not working when someone copy paste data on validation cell

    Good morning jthakrar

    A lot of problems that users run into (but not all) are usually solved by some clever bod somewhere along the line and posted for the world to marvel over. One such technique is posted here by John Walkenbach here and is both fantastically clever and simple.

    Read it and marvel at your liesure.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  10. #10
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: validation rules not working when someone copy paste data on validation cell

    Nice tip, Dominic!

+ 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