Prevent Copy Paste over Data Validation cell

try this vba

Please Login or Register  to view this content.

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

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 ??

Originally Posted by Shijesh Kumar

try this vba

Please Login or Register  to view this content.

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.

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.

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.

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.

Dairy_Queen,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

This code is limited to preventing pasting non-validated cells over validated cells. As another post noted, it also creates an endless loop if you paste from non-validated cell to non-validated cell. I needed something that doesn't allow any pasting over a validated cell.

Here's what I ended up using. Paste this into the sheet's module (the HasValidation function works as is):

Please Login or Register  to view this content.

Put this at the top of the sheet's module: (this prevents the endless loop)

Please Login or Register  to view this content.

Then use some or all of the Workbook_... routines that excel1z008339 posted below. What all of this still doesn't cover is where the user copies from outside the application and where they paste by using the Enter key. I don't know how to deal with those scenarios. Microsoft really ought to fix this limitation in data validation.

Originally Posted by Shijesh Kumar

try this vba

Please Login or Register  to view this content.