Prevent Copying Data

C

Chrisp

Is there a way to prevent users from copying data within a worksheet
while still leaving cells open for entry. My problem is they are
copying data into cells that when the data is pasted, it wipes out the
conditional formatting. Any ideas?
 
K

Ken Johnson

Hi Chrisp,
I think that the only way you can stop your users copying cells is to
prevent selection of locked cells.
If you are using xl2003 that option is available in the sheet
protection dialog. Just remove the green tick from "Select Locked
Cells" when you apply sheet protection.
If you are using an earlier version then you have to set that
worksheet's EnableSelection property to "1 - xlUnlockedCells" then
unlock the cells to which your users can input data, then protect the
worksheet. Then, the only cells that they can select are the ones you
unlocked before applying protection.

To change the worksheet's EnableSelection property right click its
Sheet Tab then select "View Code" from the popup. This takes you to the
VBA editor.
In the VBA editor go View >Properties Window, then look for and select
EnableSelection in the left hand column. Then, on the same line, in the
right hand column, click on the down arrow and select "1 -
xlUnlockedCells".
Then press Alt + F11 to return to the worksheet.
Then, after selecting all of the cells the users need to access for
input
go Format>Cells>Protection then remove the tick from Locked, then OK.
Then go Tools>Protection to protect the sheet.

Ken Johnson
 
K

Ken Wright

How about an event macro tied to the Worksheet_SelectionChange event that
clears the clipboard. Probably not foolproof, but may help.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
N

Neil

Have you tried using cell locking and protection to stop users from
destroying your conditional formatting, this would still allow then the
convenience of being able to paste data into the cells. There's a short
tutorial in the Excel section on my website that might help.

HTH

Neil
www.nwarwick.co.uk
 
Top