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

Hi Pablo,

No I don't think so, it was a while ago when I used this but
effectively I was trying to do the same as you, I didn't want users to
stop pasting in etc. but still wanted to force a particular format.

To be sure just put Application.cutcopymode = false at the end of your
code, this will do the same as when you hit escape after a value has
been pasted and effectively reset the cutcopymode so it will not
trigger next time, there may be the rare occasion when this would be
annoying for a user but I can't think of one?!

Any problems then give me a shout,

James


pablo bellissimo wrote:
Thanks for the reply james.

Could you just confirm what would happen if a user pasted a value into a
cell and then manually typed a value into the cell below. When they manually
typed the value would the cuptcopymode still = xlCopy even though the user
hadn't actually pasted into the current cell??

" wrote:

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

Hi Pablo,

Thinking about it, the answer is no as the change event of a worksheet
happens after the user has finished editing a cell but editing a cell
will actually reset the cutcopymode to false anyway so the only time
this should fire is when a user is pasting.

Cheers,

James


wrote:
Hi Pablo,

No I don't think so, it was a while ago when I used this but
effectively I was trying to do the same as you, I didn't want users to
stop pasting in etc. but still wanted to force a particular format.

To be sure just put Application.cutcopymode = false at the end of your
code, this will do the same as when you hit escape after a value has
been pasted and effectively reset the cutcopymode so it will not
trigger next time, there may be the rare occasion when this would be
annoying for a user but I can't think of one?!

Any problems then give me a shout,

James


pablo bellissimo wrote:
Thanks for the reply james.

Could you just confirm what would happen if a user pasted a value into a
cell and then manually typed a value into the cell below. When they manually
typed the value would the cuptcopymode still = xlCopy even though the user
hadn't actually pasted into the current cell??

" wrote:

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