Always paste special

[yoko]

Hi,

I have a sheet which is protected but people are still able to change cell formats etc if they use the standard paste method. I want people to be able to paste data in but it forces paste special values to stop people pasting formats etc over already formatted and locked cells.

I know I could write a macro and run it using the shortcut keys ctrl-v but then the users lose the ability to undo if they make a mistake.

Is there a way to force paste special or allow users to run a macro but be able to do undo if they make a mistake?

Cheers,

 

[mikerickson]

Alternatly, you could write a routine that formats the workbook the way that you want it and then have the Change event call that routine.

Then after each Copy/Paste, the formatting would be restored to the way you want it.

 

[x4nreh]

Alternatly, you could write a routine that formats the workbook the way that you want it and then have the Change event call that routine.

Then after each Copy/Paste, the formatting would be restored to the way you want it.

Thanks, mikerickson! That is an interesting approach. However, some of the data that needs to be pasted is a set of digits (account numbers) that may have leading zeros.

When pasting digits with leading zeros from certain applications, Excel converts it to a number and strips the leading 0s. That is a bad thing and converting it back to a text entry doesn't restore those leading 0s. Because the set of digits can be of varying lengths, we don't know how long any particular set of digits was originally, so we can't restore the leading zeros.

 

[DJHarris71]

Yes - The cut Button on the ribbon bar is still active. I agree - this code needs tobe updated to disable that ribbon. I will look into this.

 

[bpeacock73]

I believe this code is longer than it needs to be.
The 'undo' code is unnecessary because an undo can be done when a paste special... is done under normal circumstances.

It also creates a problem when you have locked cells in your workbook