Prevent paste from re-formatting cell

[BMac]

worksheet is protected and all cells except 4 are locked. Even though users
are not able to change formatting of these cells directly, I just realized
that pasting info into one of these cells changes the format (to that of the
copied info) and breaks the spreadsheet/calculator. Example: one of the
unlocked cells is a dropdown list (of months). If you incorrectly type a
value that is not on the list into the cell you will appropriately get a
customized error message and given the option to re-enter an allowed value.
However, If you paste that same incorrect value into the cell it will change
the formating of the cell, disable the dropdown box, and basically "break"
the spreadsheet. Can I prevent someone from accidently pasting info into one
of these cells. If not, how do I get the cell to keep original formatting ,
even if the pasted format is different. Thanks for you help.

 

[JBeaucaire]

If it's that critical, then diable cut/copy/paste completely in this
workbook. This website gives you code and instructions on where to put it.
Once installed, no one will be able to do that, and will receive a message
onscreen if they try.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

 

[JE McGimpsey]

Two caveats:

1) It requires macros to be enabled. If the workbook is opened with
macros off, there's no protection at all.

2) The routines won't work if the workbook is opened by a user with
MacXL. While one could substitute the default Mac keyboard shortcuts, in
MacXL, Copy alone has 7 different default keyboard shortcuts (CTRL-Ins,
Ctrl-c, Ctrl-Help, F3, CMD-Help, CMD-Ins, Cmd-c), and Paste has 4
(Shift-Del, Ctrl-v, CMD-v, F4).

In addition, in MacXL nearly any commands can be assigned to nearly
*any* shortcut key, including copy and paste.

 

[JBeaucaire]

The page referenced:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

....also includes a link to help you insure macros are enabled:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

As for the Mac stuff and or any other commands oriented copy/paste method,
I'm sure they could be addressed as they are discovered by expanding the code
offered on that page to trap more events.

 

[JE McGimpsey]

My point was that, no, they *can't* be addressed for MacXL, since a user
can have entirely idiosyncratic keyboard shortcut assignments that can't
be programmatically discovered.

Just as a plain vanilla example, I know a number of MacXL users who map
Ctrl-p to Paste (leaving the Mac-default CMD-p assigned to Print). They
then are free to assign Ctrl-v to, say, (V)alidation, or CMD-v to Normal
(V)iew.

In addition, if they have mapped any of the default shortcuts to a
different function, there's no way of programmatically determining what
that mapping is, so by disabling, say F3 (default Copy), you may have
just permanently undone the user's customization.

Not saying it's a huge risk (which is why I used the relatively mild
word "caveat"), but it *is* a vulnerability.