Locking Cells from Cut and Paste

[chaosincarnate]

Is there a way to lock excel sheets from having someone cut and paste into the sheet. Don't want people to copy info from another excel page/workbook and paste it into mine. Want them to manually enter the data.

 

[prasaddn]

Hi,


I had similar requirements few days back, to control data validation being deleted. Paste may be done in many ways on the sheet like:

Control+v

Control+Insert

Shift + Insert

Enter

And of course various menu items and toolbar buttons, which may be located anywhere.


Luckily, we have solution thru VBA, check this link...

http://www.jkp-ads.com/Articles/CatchPaste.asp


Regards,

Prasad

 

[chaosincarnate]

Thanks...


Now i have to figure out how to use VBA .


Gives me something to play with.....

 

[Jack]

Hello Chaos

I have the same issue with a roster that relies on validation which can easily be wrecked by copy/cut/paste drag and fill. I got this code from somewhere a few years back which you copy into the VBA Module ThisWorkbook to work. It's pretty tight I find and only allows manual entry - no drag or drop or anything. If you need to do some copying and pasting again you have to simply delete it from the module and copy back when you are finished. Apologies to who ever out there wrote this brilliant code.


Private Sub Workbook_Activate()

Application.CutCopyMode = False

Application.OnKey "^c", ""

Application.CellDragAndDrop = False

End Sub


Private Sub Workbook_Deactivate()

Application.CellDragAndDrop = True

Application.OnKey "^c"

Application.CutCopyMode = False

End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)

Application.CutCopyMode = False

Application.OnKey "^c", ""

Application.CellDragAndDrop = False

End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

Application.CellDragAndDrop = True

Application.OnKey "^c"

Application.CutCopyMode = False

End Sub


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Cancel = True

MsgBox "Right click menu deactivated." & vbCrLf & _

"Cannot copy or ''drag & drop''.", 16, "For this workbook:"

End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.CutCopyMode = False

End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.OnKey "^c", ""

Application.CellDragAndDrop = False

Application.CutCopyMode = False

End Sub


Good luck

John

 

[shajan]

This is a wonderful VBA code. Thanks for sharing it John. Further, if you password protect it, no one will be able to view or delete it. Am I right ?

 

[Jack]

Hi shaman yes you are right, I forgot to mention this. But this code does makes the spreadsheet cumbersome to use so it best suits a situation of minimal data entry. For instance in my roster after you have set up week 1 for ease of data entry you then want to copy week 1 to week. 2. So I had to develop code to go in and delete this above code do the copy then put the code back again. It's not as good as I like it to be yet as is a two step process.