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
 
Back
Top