Annoying Paste Bug (Excel 2003)

[Burnnie Holliday]

I have a worksheet that I reproduce every wokday for all supervisors to enter
data in. Some of these cells are merged for the sake of formatting, and have
various formats set in them that are protected. However, the protection just
ignores formats set when the user pastes information. Cells become unmerged,
previously unlocked cells become locked, and borders completely disappear.
Is there a way to disable the ability to paste information outside of values
only?

 

[jamescox]

You may get a better answer from someone else, but it should be possible
by using the worksheet SelectionChange event and the worksheet Change
event to cache what the format in a cell was before it was changed.
This gets complicated fast, because you have to deal with copy and paste
operations and worksheets don't have a paste event. You may also have
to wind up suppressing Excel's 'move to another cell after cell entry is
complete' "reflex".

A much simpler way would be to provide a data entry region (perhaps
even on the same page as the formatted report) where the users can type
or paste any sort of formatted information and the formatted area's cell
formulas merely reference the input zone cells.

You would have to protect the formatted area, so your user's can't type
or paste values into those cells, but that can be done.

Good luck on this one!