Copy and paste

P

Pietro

Hi all,
I have some columns(A,B) in my Excel sheet that don't allow to the users to
insert a date prior to today's date,as i used data validation to achieve
this,but actually users write a date in other columns and paste them in the
columns(A,B) in order to put a date that's prior to today's date,how can i
solve this problem?
 
S

Sandy Mann

How about a sheet event code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 2 Then Exit Sub

Application.EnableEvents = False
If Target.Value2 < Int(Now) Then
Target.Value = ""
MsgBox "Please do not pre-date"
End If
Application.EnableEvents = True

End Sub

place it in the sheet module for the sheet you are using.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
G

Gary''s Student

Say A1 is a cell into which the user should enter a date, but not a date
prior to today's date.

In A1 enter:
=IF(TODAY()<Z1,Z1,TODAY())
Then protect A1 and tell the user to enter dates in Z1. Because A1 is
protected, the user can't change it in any way. If the user screws up the
data in Z1, you get today as a default.

You don't have to be much smarter than a user, even a little bit smarter
will do.
 
Top