Validation, Date

[EDSTAFF]

I am using Validation to limit dates entered in cells of column.
Criteria
Allow: Date
Data: <=
End Date: =TODAY()

This works for direct entry in cell as it gives message indicating "Future Date" and doesn't allow entry.

However, a user can still cut/paste a future date in the cell, thus bypassing the validation rule. One can also use a series of dates and drag downward (1/2005, 2/2005...drag to create 3/2005 +) and again bypass the Validation rule for dates.

Anyway to prevent copy/paste and dragging to bypass the validation rules without protecting the worksheet? I need the sheet unprotected in order for List/Autofilter to function.

Thanks,

L.M.

 

[Greg Truby]

This would handle the pasting problem. NoCopyZone is a named range corresponding to the cells that are being validated. How to prevent autofill isn't occurring to me off the top of my head.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [NoCopyZone]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[EDSTAFF]

Thanks Greg, I'll try that solution.

For what its worth, I find Validation to be very deficient if one can bypass it so easily with copy/paste and autofill. Is this simply an oversight by MS or am I just expecting too much from the Validation procedure?

Thanks for taking a look,

L.M.