Prevent Paste over Data Validation

**dbodd** · 02-22-2007, 08:51 AM

When users paste data into cell , I need to protect data validation/ conditional formatting etc. How can one do this?

**VBA Noob** · 02-22-2007, 09:10 AM

Hi,

This link may help

http://www.j-walk.com/ss/excel/tips/tip98.htm

VBA Noob

**dbodd** · 02-22-2007, 10:12 AM

Hi there , Many Thanks. I actually tried this code, but it did not work. What do you mean by wrap code with # ? ?

**VBA Noob** · 02-22-2007, 10:20 AM

When you create new posts and need to add code click on the # symbol which is in the toolbar above where you type the text

Here's an example

Not wrapped

Sub Macro9()
ActiveWindow.ScrollColumn = 1
End Sub

Wrapped

Please Login or Register  to view this content.

VBA Noob

**VBA Noob** · 02-22-2007, 10:23 AM

Where do you post the code

Are the users deleting entire rows or columns ??

That's the only way it won't work

VBA Noob

**dbodd** · 02-22-2007, 10:47 AM

Hi There , I copied and pasted this code off the Web link into the VBA window of the same sheet in which I am trying to protect the data validation cells.

**VBA Noob** · 02-22-2007, 10:56 AM

and you followed this instruction from the website

Important:
Ensure that every cell in the ValidationRange actually contains Data Validation

VBA Noob

**dbodd** · 02-22-2007, 11:04 AM

Yes I Did. I am truly lost.

**VBA Noob** · 02-22-2007, 11:18 AM

Mine works fine (See attached)

Did you rename the range to ValidationRange

VBA Noob

**dbodd** · 02-22-2007, 12:09 PM

Hi There , I see your sheet works if you copy and paste into one of the cells , but it does not work if I paste special VALUES as it allows other info not on list to be placed in cell. Also in my case I don't have a list to select, but a custom - formula valadation. I am off home now and will continue tomorrow. Thanks for all your help .

**VBA Noob** · 02-22-2007, 12:22 PM

Here are what is covers

Limitations
This procedure handles the following operations, which would normally delete the Data Validation rules:

Cutting/pasting data
Copying/pasting data
The Edit - Clear - All command
The Edit - Delete command

If it's not here then it doesn't prevent it

VBA Noob

**dbodd** · 02-23-2007, 01:06 AM

I felt if it work for Copy/Paste it would work for Paste Special as well. Originally I thought that the Data Validation would still be valid even with Copy Paste. This is what I gathered from this link : http://www.alltipsandtricks.com/blog...on-in-ms-excel
where they say : Tip: the validation property is maintained when applying the Copy – Paste command. Once again , Thanks for your time.

**PeruvianSkies** · 04-03-2009, 10:11 AM

Originally Posted by VBA Noob

Hi,

This link may help

http://www.j-walk.com/ss/excel/tips/tip98.htm

VBA Noob

Hey, I know this is an old thread but I am trying to solve this exact problem.

That link worked for me but there's a problem with it, it does not work if a table has different validations. Does anyone know how I could overcome this problem?

**shg** · 04-03-2009, 10:40 AM

Please take the time to read the forum rules, and then start your own thread.

**PeruvianSkies** · 04-03-2009, 10:59 AM

Ah sorry shg. Created another thread now.

**webtronix** · 05-27-2011, 05:43 AM

Originally Posted by VBA Noob

Mine works fine (See attached)

Did you rename the range to ValidationRange

VBA Noob

Thank you - you're a life saver.....

**tone640** · 07-01-2011, 10:09 AM

Tried this, but when I make a change, the macro gets stuck in a loop because at the 'Application.Undo' step, it returns to the beginning of the sub. The only difference is my named range is 'ValidationRangeThu' (which I have also used in the code).

I have tested the workbook by VBA Noob, and mine is the same code in the sheet module (apart from the above).

Any ideas?

Edit - Any change to the sheet triggers the loop, and I have also tried it with the named range as 'ValidationRange'.
Edit - It seems it doesn't like non-contiguous ranges, although the description says they are fine. It works correctly with contiguous ranges, but I don't want to have to make multiple ranges with multiple codes.