Force Paste Values - to avoid losing data validation

[KerriO]

I have a spreadsheet that has lots of data validation - all different types - lists, numbers, dates etc.
I am aware that pasting into these cells will remove the data validation. I can't just prevent users from pasting though as pasting data is a vital part of the spreadsheet's functionality.
The only thing I can think of is to prevent users from using the normal paste function - and only allow "paste values". Is anyone able to advise how to achieve this?

 

[Jdog12]

I have a spreadsheet that has lots of data validation - all different types - lists, numbers, dates etc.
I am aware that pasting into these cells will remove the data validation. I can't just prevent users from pasting though as pasting data is a vital part of the spreadsheet's functionality.
The only thing I can think of is to prevent users from using the normal paste function - and only allow "paste values". Is anyone able to advise how to achieve this?

Let's assume your data list is in column "I".

Right click on sheet name and select view code, then add the codes below. Once the code is there, user cannot copy and paste anything to your data validation list in column I , but user can copy and paste data to any other columns.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("I2:I100")) Is Nothing Then

Application.CutCopyMode = False
End If

End Sub

 

[KerriO]

Thank you so much for this. Unfortunately it doesn't resolve the issue. This code prevents all forms of paste. I still need users to be able to paste - but only "paste special" (to avoid breaking the validation rules).

 

[hajiali]

Perhaps a Sheet Code that takes care of the DV. I notice you mentioned that you have a lot of DV on the spread Sheet. Each one will need to be converted to Sheet Code that will still allow the normal Copy & Paste however, it will not allow users to copy & paste invalid Data into the Cells you have the DV. Please proved Specific Cells, Rows, or Columns and what DV you have. If you enter your Data using XL2BB it will provide us the DV formula you have for each and we can better assist you.

 

[awoohaw]

I know this would be time consuming to set up if you have lots of DV cells. But, perhaps this could be used. Place Conditional Formatting on Adjacent cells, and/or a formula in other adjacent cells that advises them to undo their paste. Something Like this:

Mr Excel Questions.xlsx
	A	B	C	D
1				Valid Letters
2	Letter	J		A
3				D
4	Z			G
5				J
6				M
7				P
8				=IF(ISNA(MATCH(B2,Valid_Letters,0)=TRUE),"PLease Undo And Paste Valid Values","")
DV and Conditional Formating

Cell Formulas
Range	Formula
C2	C2	=IF(ISNA(MATCH(B2,Valid_Letters,0)=TRUE),"PLease Undo And Paste Valid Values","")
D8	D8	=FORMULATEXT(C2)

Named Ranges
Name	Refers To	Cells
Valid_Letters	='DV and Conditional Formating'!$D$2:$D$7	C2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	Expression	=ISNA(MATCH(B2,Valid_Letters,0)=TRUE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Valid_Letters

 

[kvsrinivasamurthy]

This works . I have tested.
Worksheet event is used. Code:

Public X

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Y
Application.EnableEvents = False
On Error Resume Next
Y = Target.Validation.Type
If Not IsEmpty(X) And IsEmpty(Y) Then
    MsgBox "Cell is validated. Hence use only Paste Special - Values."
    Application.Undo
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
X = Target.Validation.Type
End Sub

How to use workheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[kvsrinivasamurthy]

Code in post#6 is When single cell is pasted.

 

[kvsrinivasamurthy]

Try this for any number of validation cells.

Option Explicit
Public X

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Y
Application.EnableEvents = False
On Error Resume Next
Y = 0
Y = Target.Validation.Type
Debug.Print X
If X <> 0 And Y = 0 Then
    MsgBox "Cell is validated. Hence use only Paste Special - Values."
    Application.Undo
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
X = 0
X = ActiveCell.Validation.Type
End Sub