Copy prevention technique??

Posted by Dave R. on June 26, 2001 6:01 AM

I have a worksheet which has many protected cells. My desire is for these to remain protected when other users utilize this worksheet. If I copy the tab to another worksheet in my existing workbook or to another workbook, the cell protection remains in place. However, if I simply select all cells and copy them to another worksheet, the protection is not existent in the new worksheet. Is there a way to insure that these attributes (cell protection) automatically go along with the new data, or better, is there a way to variably "turn off" copying with an attribute setting.

Posted by Dax on June 26, 2001 6:07 AM

Hi,
One way you could do it is this.

Right click the worksheet tab where your protection is and use this code: -

Private Sub Worksheet_Deactivate()
Application.CutCopyMode = False
End Sub

HTH,
Dax.

Posted by Ben O. on June 26, 2001 6:49 AM

Very sneaky :) (n/t)

Posted by Ivan F Moala on June 26, 2001 11:15 PM

Dax
Good method !!!....however to stop the user from
copying to another workbook then use the code
in the Thisworkbook object eg.

Private Sub Workbook_Deactivate()
If Application.CutCopyMode = xlCopy Then
MsgBox "Copy mode has been deactivated"
Application.CutCopyMode = False
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Application.CutCopyMode = xlCopy Then
MsgBox "Copy mode has been deactivated"
Application.CutCopyMode = False
End If
End Sub

Ivan