+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 19

Prevent Copy Paste over Data Validation cell

  1. #1
    Registered User
    Join Date
    12-23-2008
    Location
    Chennai,India,Tamilnadu
    Posts
    70

    Question Prevent Copy Paste over Data Validation cell

    Hello -

    I set up a spread*** with drop-down menus for some of the cells. The
    user can only select values from that list and an error message pops up
    when something is typed it that is not in the list (via Data Validation

    Error Alert).


    So this works all fine ... except if the user copies and pastes a cell
    with a different drop-down menu into a cell overwriting the existing
    validation.

    I cannot totally lock the work*** and if I lock the cells with the
    drop-down menus nothing can be changed. So how can I prevent this from
    happening? Is there a way to block all copy/paste operations that are
    copying the validation? I know that with the Paste Special I can have
    cells copied without the validation.

    Thanks for any comments!
    M.Velmurugan

  2. #2
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717

    Re: Prevent Copy Paste over Data Validation cell

    try this vba

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-23-2008
    Location
    Chennai,India,Tamilnadu
    Posts
    70

    Re: Prevent Copy Paste over Data Validation cell

    Yes it is working with this VBA code.

    But i am trying to do without VBA code?
    Is it possible to do without VBA Code?

    Thanks
    M.Velmurugan

  4. #4
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717

    Re: Prevent Copy Paste over Data Validation cell

    Other option would be to protect the cell containing validation...
    but in this case... user may not be able to change the value in those cells

  5. #5
    Registered User
    Join Date
    07-20-2009
    Location
    Noida, India
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Prevent Copy Paste over Data Validation cell

    i have the same prob...though i am new at VBA...please guide if i need to do some changes in this VBA code or just copy paste this in my VBA editor?

  6. #6
    Registered User
    Join Date
    09-01-2009
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Prevent Copy Paste over Data Validation cell

    This code doesn't work with non-contiguous range.

    Quote Originally Posted by Shijesh Kumar View Post
    try this vba

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-27-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Prevent Copy Paste over Data Validation cell

    This is an old thread but still valid for me.
    But I still have errors

    I have Office/Excel 2010 and while the code works to prevent pasting data into the cells with data validation.....
    it's also preventing me from modifying any cells which DON't have data validation.
    For cells without DV, I get the stop error multiple times and have to hold down the escape key until all the messages stop appearing.
    Even double clicking inside a non DV cell causes the stop message to appear.
    Sometimes I get a debug option which highlights the 'End If' in the Worksheet_Change sub

    I've even cleared all cells of their data validation and the problems are the same.
    It's like the HasValidation function isn't recognising the Validation.Type of the normal non DV cells

    Does the code need to be updated for Excel 2010 ??




    Quote Originally Posted by Shijesh Kumar View Post
    try this vba

    Please Login or Register  to view this content.
    Last edited by GavCol; 06-27-2011 at 11:50 PM.

  8. #8
    Registered User
    Join Date
    07-01-2011
    Location
    méxico, d.f.
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Prevent Copy Paste over Data Validation cell

    HI, I KNOW THIS IS AN OLD THREAD BUT THIS MAY HELP SOMEONE

    Please Login or Register  to view this content.
    THIS WORKS COUNTING THE CELLS IN THE SELECTION AND THE CELLS WITH DATA VALIDATION IN THAT SELECTION, IF THE NUMBERS ARE DIFFERENT, THE ACTION IS UNDONE. I TRIED IT IN EXCEL 2010, HOPE IT'S USEFUL

  9. #9
    Registered User
    Join Date
    07-01-2011
    Location
    méxico, d.f.
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Prevent Copy Paste over Data Validation cell

    sorry, my code needed the indication of where the data validation is

    Please Login or Register  to view this content.
    IN THE EXAMPLE, THE DATA VALIDATIOS IS IN COLUMNS 1,2 OR 3.
    Last edited by qafqunam2001; 07-01-2011 at 06:53 PM.

  10. #10
    kalpanaceo
    Guest

    Re: Prevent Copy Paste over Data Validation cell

    I can suggest a company "Informatics Outsourcing" is a Offshore Market Research Service Company. They are providing clinical Research and Market Research Data validation Service.

  11. #11
    Registered User
    Join Date
    10-29-2011
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Prevent Copy Paste over Data Validation cell

    using this code where exactly does the code specify the range you are talking about? If the range I am using is B9:Q16 how would I edit the code. I am not familiar with VBA and this is my first attempt to use it. Also, the worksheet is protected.... I have all the cells in my worksheet locked with the exception of the range with the data validation.
    Last edited by excel1z008339; 12-19-2011 at 03:03 PM. Reason: added information

  12. #12
    Registered User
    Join Date
    05-29-2012
    Location
    canada
    MS-Off Ver
    Excel 2003, excel 2010
    Posts
    1

    Re: Prevent Copy Paste over Data Validation cell

    Hi I am currently using Excel 2003 and preparing a template for the information I gather. There are certain columns with list validation and certain formula validation. I do not want my users to copy paste information in these validated cells. In case of list validation I would like them to select from the dropdown list. Could you help me code a vba program for this feature

    Thanks

  13. #13
    Registered User
    Join Date
    10-29-2011
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Prevent Copy Paste over Data Validation cell

    This worked for me, of course you must make sure you enable macros.
    Copy this code then paste it into the ThisWorkbook code module...

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by arlu1201; 11-05-2012 at 03:50 AM.

  14. #14
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Prevent Copy Paste over Data Validation cell

    excel1z...,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Registered User
    Join Date
    10-29-2011
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Prevent Copy Paste over Data Validation cell

    Thanks for adding the code tags.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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