Stopping Users Cutting & Pasting

[Jon B]

Hi, Quick bit of help needed if possible:

I've got a workbook that is mostly protected, but some cells need to be left unlocked for users to type in. My question is how I stop users cutting & pasting from these cells and messing up the formulas?

I've used a Worksheet_BeforeRightClick macro, but they can still use the icons or menus.

Any Ideas? (I get the horrible feeling I'm missing something really obvious)

Thanks

 

[Iridium]

http://www.mrexcel.com/board2/viewtopic.php?p=206642#206642

 

[dk]

Hi,

You can get somewhere in achieving this by trying the following:-

Place these procedures in the workbook code module of your workbook (see the Workbook Modules section from here to see how to do that) :-

<pre>Private <FONT COLOR="#00007F">Sub</FONT> Workbook_Deactivate()
Application.CutCopyMode = <FONT COLOR="#00007F">False</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>


<FONT COLOR="#00007F">Private</FONT> <FONT COLOR="#00007F">Sub</FONT> Workbook_SheetChange(<FONT COLOR="#00007F">ByVal</FONT> Sh <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Object</FONT>, <FONT COLOR="#00007F">ByVal</FONT> Target <FONT COLOR="#00007F">As</FONT> Range)
Application.CutCopyMode = <FONT COLOR="#00007F">False</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>


<FONT COLOR="#00007F">Private</FONT> <FONT COLOR="#00007F">Sub</FONT> Workbook_SheetDeactivate(<FONT COLOR="#00007F">ByVal</FONT> Sh <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Object</FONT>)
Application.CutCopyMode = <FONT COLOR="#00007F">False</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>


<FONT COLOR="#00007F">Private</FONT> <FONT COLOR="#00007F">Sub</FONT> Workbook_WindowDeactivate(<FONT COLOR="#00007F">ByVal</FONT> Wn <FONT COLOR="#00007F">As</FONT> Window)
Application.CutCopyMode = <FONT COLOR="#00007F">False</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>
</pre>

This should effectively disable most ways of copying/cutting and pasting (it's by no means foolproof though). You may want to only use if you want to allow certain copying e.g. remove the Workbook_Deactivate procedure if you want to allow your users to copy from your workbook to another.

 

[Jon B]

Thanks for the help guys, all sorted now.