Preventing copy/paste on validated cells

  • Thread starter Thread starter mbp
  • Start date Start date
M

mbp

Hi
I've got a column of cells with validation.
If a user instead of typing values into the cells, copy/paste from another
cell, the origial validation settings are overwritten.
Is there any way to prevent the user to do this, otherwise validation is not
worth much.
 
See other reply for prevention of copy/paste with code from John Walkenbach.

Alternative...........allow copy/paste but retain original formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Const WS_RANGE As String = "A:A"
Dim myValue
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End If
End Sub


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Protect validation but allow drop down selection 1
Protect Data validation cells from copy paste 3
Data Validation/Cell Protection Question 2
How to prevent Cut&Paste to change style and data validation on protected sheet 1
Data Validation 2
Data Validation - repost 2
Disable mouse clicks on a range of cells with validations 1
data validation 1

Back
Top