Data validation

[hermithead]

I recorded a simple Data validation macro that displays a stop message when excel detects data outside a specific date range ie 01.07.2009 - 31.07.2009. The problem is its not working.
The spreadsheet works by the user pasting July data into the sheet and then the data validation macro runs through the column with the date in it to ensure its the correct month.

The problem is the data validation only works if you type the incorrect date in to the cell and not if you paste it. Any ideas?

[shg]

Maybe seeing the code would help ...

[hermithead]

Please Login or Register  to view this content.

[shg]

That's true, data validation doesn't work when you paste.

I thought you meant you were doing the validation in code, which is probably what you'll need to do. The alternative is writing code to flush the clipboard with every change in selection.

[hermithead]

I wonder if there is an IF statement that could be used to substitute the data validation function?? ie look through this array for this criteria and if false generate a msg box informing the user the data pasted is incorrect.

[hermithead]

I think I have a solution...

Paste the data into Column B then run a simple cell reference in column A to the data in column B. This cell reference will make the cell active and trigger off the data validation rule.

A few extra steps to the macro but the simplest answer I have found so far (and it wasn't by Googling haha)