Data validation doesn't work if contents is added by pasting

J

jeff.taylor

Hi

I've data validation set so that only the number 1 can be entered, but
even this cannot be entered if an adjacent cell is a certain value.
this works for direct entry, but not if the value is pasted in or added
using the "drag handle".

Is there any way to stop this happening as I just know somewones going
to try and fill all the cells by draging the values down.

Regards

Jeff
 
J

Jim Cone

Jeff,
You could disable "Allow cell drag and drop" in Tools | Options or...
copy the following code, right click the sheet tab and choose
'view code'. Paste the code into the module.
It does not take the "adjacent" cell in to account however.
Also, any formula that returns a value of 1 is allowed.
Change B5 to the correct cell.

'----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ChangeErr
Application.EnableEvents = False
If Target.Address = "$B$5" Then ' <<<correct
If Len(Target.Value) Then
If Target.Value <> 1 Then Target.ClearContents
End If
End If
ChangeErr:
Application.EnableEvents = True
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



<[email protected]>
wrote in message
Hi
I've data validation set so that only the number 1 can be entered, but
even this cannot be entered if an adjacent cell is a certain value.
this works for direct entry, but not if the value is pasted in or added
using the "drag handle".
Is there any way to stop this happening as I just know somewones going
to try and fill all the cells by draging the values down.
Regards
Jeff
 
Top