data validation

[ericlch16]

i have a cell A1 which has data validation so that it is either 1 or 2. if user enter 3, there is an error msg prompting the user to re-enter number 1 or 2... this is fine.

B1 contains 3. if a user copy B1 and then paste it in A1... then there is no msg prompting. so the user manager to enter 3 in A1 by copy/paste from another cell. how can i prevent that?

 

[Smitty]

Unfortunately, there's no easy way around that.

 

[grizz]

if your drop down is in sheet 1 A1 put this code in sheet 1 and it will prevent paste in A1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

 

[T. Valko]

if your drop down is in sheet 1 A1 put this code in sheet 1 and it will prevent paste in A1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

That takes care of cut/copy and paste...

But you can still drag -n- drop!

It might be better to just forget about using data validation and use a change event macro.