Preventing copy/paste on validated cells

[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.

 

[מיכ×ל (מיקי) ×בידן]

Make use of JW tip:
http://j-walk.com/ss/excel/tips/tip98.htm
micky

 

[Gord Dibben]

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