Protect worksheet-deactivate 'save as, cut/copy/paste option

[gangani23]

I have protected excel worksheet with only few columns unprotected, which users are supposed to complete, save and return.

BUT people just 'copy/paste' or use 'save as' option and protection is no more working. Please let me know if there is anyway I could protect 'cut/copy/paste' and 'save as' options in my worksheet

Help will be highly appreciated

 

[Tom Urtis]

Re: Protect worksheet-deactivate 'save as, cut/copy/paste op

Regarding save as...

Try this - - place the following procedure in your workbook module. Find the little Excel workbook icon near the upper left corner of the window, just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Select Case True
Case SaveAsUI
MsgBox "Sorry, you may not ''Save as'' this workbook." & vbCrLf & vbCrLf & "You may only save it, keeping its original name.", 48, "''Save as'' not allowed."
Cancel = True
Case Else
Exit Sub
End Select
End Sub



Regarding cut copy paste etc...

Ivan Moala has this on his page here...
http://www.xcelfiles.com/VBA_Quick13.html

...and this may dissuade honest people, though there are no guarantees (goes in the workbook module; weed out what you don't want):

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

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

 

[gangani23]

Re: Protect worksheet-deactivate 'save as, cut/copy/paste op

GREAT!

Thanks a lot Tom Urtis. I tried everything to resolve this thing but your solution worked great!

Thanks a lot ray:

 

[gangani23]

Re: Protect worksheet-deactivate 'save as, cut/copy/paste op

The only way it can be copied is by Windows explorer. I could go there click on copy and it can be copied with different name.

Is there any way I could protect copy/cut/paste option from Windows Expolore?

 

[Tom Urtis]

Re: Protect worksheet-deactivate 'save as, cut/copy/paste op

No, sorry, as far as I know, Excel cannot protect itself against someone going into Explorer, selecting a file name, and renaming it there.

If someone does change the file name that way, maybe what you could do ahead of time is to place a conditional statement in an open event to evaluate the file name. Example, if that that name is different than what you want it to be, keep all worksheets VeryHidden, or auto close it, or something else that renders the file useless.

Another option not practical in your case because others will be using the file, is to keep it stored in a secret or hidden folder so it cannot be accessed from Explorer.

 

[gangani23]

Re: Protect worksheet-deactivate 'save as, cut/copy/paste op

Thanks for the response.

That's a good idea to put some kind of conditional statement. Here is the situation: I am sending files to users, they are giving their input in assigned columns, saving it and returning it to me. Is there anyway I could find that this is the same file, which I send them? And they've not copy/paste it from Explorer? Or anyway which will tell me that somebody has copy/paste the file and the one which I am getting is not the one which I send them?

Please let me know whether it is doable.

Thanks

 

[gangani23]

Re: Protect worksheet-deactivate 'save as, cut/copy/paste op

Bummper

Once I close the file and reopen it 'cut/copy/paste' and 'save as' options activate again. Hope could I deactivate 'cut/copy/paste' and 'save as' options permanently in my specific worksheets?

Please help me