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


"mbp" wrote:

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

On Thu, 17 Dec 2009 03:36:02 -0800, mbp
wrote:

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.