Hi,

Using the worksheet change event you can capture the whether the user
has something copied by testing for:

If Application.CutCopyMode = xlCopy Then

This will only = xlcopy when pasting, you are then able to change the
result to fit your formatting as you know what cell the user is in etc.
so you could test the length and append a zero or alternatively
pastevalues which will then take on your validation.

James


pablo bellissimo wrote:
Hi All,

I'm a total newbie with VBA so please be patient!
I need to assign data validation to a single column in a worksheet to ensure
that users can only enter 11 digits but the first one must be a zero. I can
force the text length to be 11 which is fine but when users paste a list
(from another sheet) it overwrites the validation, and drops the leading zero
(due to it being a number).
I can't just change the format to display the leading zero as I need to feed
the full 11 digits into another system.

In summary, does anyone have a bit of code that will prevent users from
pasting anything into the column that does not match the validation criteria?
I dont want to just turn off the paste functionality.

Any help would be greatly appeciated.

Pablo