Prevent Copy & Paste From Overwriting Data Validation

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using an article found here: http://www.j-walk.com/ss/excel/tips/tip98.htm to prevent the copy and paste action from overwriting cells which contain 'Data Validation'.

However when I try to run the code I receive the following error: 'Variable not defined' with this line highlighted as the problem: x = r.Validation.Type

I understand why the error occurs, but I'm unsure how to declare this variable.

I just wondered whether someone could possibly look at this please and offer a little guidance on how I need to declare this.

Many thanks and kind regards

Chris

[SamT]

Dim x As Long
Dim r As Range

[hobbiton73]

Hi @SamT, thank you for taking the time to reply to my post and for putting the solution together.

Your solution works great, thank you, but I've come across a problem when trying to implement this into my exisiting code which I'm just wondering whether you may be able to help me with please.

When a user enters their name, the following code is activated:

With Target
Select Case True

Case .Column = 2
If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
.Offset(, 1).Value2 = "Yes"
.Offset(, 2).Value2 = "--Select--"
.Offset(, 3).Value2 = "--Select--"
.Offset(, 4).Value2 = "--Select--"
.Offset(, 5).Value2 = "Enter your FTE"
.Offset(, 6).Value2 = "C&R"
.Offset(, 7).Value2 = "--Select--"
.Offset(, 17).Value2 = "Enter the name of your Line Manager"
End If

Case Else
End Select
End With
As you can see this prepopluates certain fields, providing instructions to the user on what information needs to be entered.

The problem I have is that this line : .Offset(, 7).Value2 = "--Select--" falls in the 'Validation Range' which I'm using the code you kindly helped me with, and so correctly displays the 'Copy and Paste' error message when the code tries to populate this field.

Could you perhaps tell me please, is there a way in which I can incorporate the two pieces of code together.

Many thanks and kindest regards

Chris

[mdmackillop]

If you want to allow the new value, try this which prevents the Overwrite code from runnibg.
Application.EnableEvents = False
With Target
Select Case True
Case .Column = 2
If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
.Offset(, 1).Value2 = "Yes"
.Offset(, 2).Value2 = "--Select--"
.Offset(, 3).Value2 = "--Select--"
.Offset(, 4).Value2 = "--Select--"
.Offset(, 5).Value2 = "Enter your FTE"
.Offset(, 6).Value2 = "C&R"
.Offset(, 7).Value2 = "--Select--"
.Offset(, 17).Value2 = "Enter the name of your Line Manager"
End If
Case Else
End Select
End With
Application.EnableEvents = True

[hobbiton73]

Hi @mdmackillop, thank you very much for taking the time to reply to my post and for the solution.

Unfortunately, this doesn't quite have the efect I would like. When I enter a name, the code populates the given fields, but the 'Validation' error message is still displayed.

Many thanks and kind regards

Chris

[mdmackillop]

Can you post a sample workbook?

[SamT]

One or the other

If.Offset(, 7).Address <> r.Address Then .Offset(, 7).Value2 = "--Select--"

If.Offset(, 7).Validation.Type <> x Then .Offset(, 7).Value2 = "--Select--"

[hobbiton73]

Hi @SamT, thank you very much for your continued help with this.

Having spent quite a bit of time on this today, the requirements for the spreadsheet concerned, have changed, which means that I've simply been able to remove the 'Cut, Copy and Paste' function for the whole column, rather than tackling the 'Cut, Copy and Paste' function for cells with the data validation.

But I just wanted to say thank you for all your time, trouble and effort with this, it is greatly appreciated.

Kind regards

Chris

[hobbiton73]

Hi @@mdmackillop, thank you very much for your continued help with this and my apologies for not sending the file yesterday.

I've spent quite a bit of time on this today and the requirements for the spreadsheet have changed. This means that rather than trying to tackle the 'Cut, Copy and Paste function for cells with data validation, I've been able to implement the removal of the 'Cut, Copy and Paste' function for the whole column.

But I wanted thank you for all your time, trouble and effort, it is greatly appreciated.

Kind regards