+ Reply to Thread
Results 1 to 11 of 11

Copying range not triggering data validation list rule

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,534

    Copying range not triggering data validation list rule

    hi,

    i am trying to avoid situation when user want to copy and paste something into range when i have DV lists.

    So for example i have DV rule like:

    Please Login or Register  to view this content.
    but when i am pasting for example string "ddsdssd" it is working! DV list is not triggered.
    Can anybody had the similar issue?

    Best,
    Jacek

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,092

    Re: Copying range not triggering data validation list rule

    If you copy and paste a cell into a cell with DV, you overwrite the Target cell AND its DV.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,534

    Re: Copying range not triggering data validation list rule

    Thank you TMS,

    it is possible to trigger it somehow?

    Best,
    Jacek

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,092

    Re: Copying range not triggering data validation list rule

    Well, the paste should trigger a Worksheet Change event so you could pick it up there, check the cell and reinstall the DV in the cell.

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,534

    Re: Copying range not triggering data validation list rule

    ok reinstall DV in the cell still will allow to keep wrong value there.

    If you have for example 1 in cell and DV rule is >1 and you will paste this DV rule into this cell there will be still 1 and DV list will be not triggered.

    Best,
    Jacek

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    2,839

    Re: Copying range not triggering data validation list rule

    You'll need to validate the cell after reinstating the DV rule.
    Rory
    There's a row going on down near Slough

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,534

    Re: Copying range not triggering data validation list rule

    Thank you Rory,

    i saw you on stackoverflow - nice picture

    So i can not trigger copied DV lists and i have to write code by my own wth DV rule?

    Best,
    Jacek

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    2,839

    Re: Copying range not triggering data validation list rule

    DV doesn't get applied to data already in the cell. All you need to do is check the Validation.Value - if it's True then the data meets the rules.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,534

    Re: Copying range not triggering data validation list rule

    ok thank you,

    what is validation.value and how to use it ?

    so you can go through all range, and somehow get validation.value to check if it is true?

    Best,
    Jacek

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    2,839

    Re: Copying range not triggering data validation list rule

    The Validation property of a range returns its data validation. If the Value property is True, then the data passes the validation rule.

  11. #11
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    1,534

    Re: Copying range not triggering data validation list rule

    ok great thank you very much! I will test it!

    Jacek

+ 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