Disabling "Save" and "Save As" in Excel 2010

[TheRealBigB]

So I tried this code found in a Disabling "Save" and "Save As" in Excel 2007 thread but it didn't seem to work for me in Excel 2010. It's certainly possible I did something wrong. Any suggestions?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "The 'Save As' function has been disabled."
Cancel = True
End Sub

 

[Domenic]

Where did you place the code? The code needs to be place in the 'ThisWorkbook' module, not a regular module. Note, however, to simply disable "SaveAs", and not "Save", try the following instead...

[font=Verdana][color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeSave([color=darkblue]ByVal[/color] SaveAsUI [color=darkblue]As[/color] [color=darkblue]Boolean[/color], Cancel As [color=darkblue]Boolean[/color])
    [color=darkblue]If[/color] SaveAsUI = [color=darkblue]True[/color] [color=darkblue]Then[/color]
        MsgBox "The 'Save As' function has been disabled."
        Cancel = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
[/font]

 

[TheRealBigB]

'ThisWorkbook' was the answer, I had in Sheet1. Thank you!!!

 

[Domenic]

You're welcome! Thanks for the feedback!

 

[TheRealBigB]

Almost there, thanks for the help so far. My remaining problem is that during testing I found that I can still paste text and numbers using Ctrl-V (I get whatever was in the buffer) into protected cells even with this code and Data Validation turned on to only accept whole numbers between 0 and 1000. Otherwise, the disabling of copy & paste plus right clicking and Data Validation to prevent invalid data entry works fine. I'm clearly missing something. Any ideas?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "The 'Save As' function has been disabled."
Cancel = True
End Sub
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 / paste 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

 

[Domenic]

See if the following helps...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

 

[TheRealBigB]

Thanks Domenic. I'm a very novice VB user in Excel, so I'm working through your suggestions. That one was more complicated for me. I don't like to use code snippets without trying to understand what they do.

It would be great if I could also turn off the Home menu bar Clipboard with its copy / paste functionality. Plus I still have to work out a good way to automatically turn on Macros. The one linked from the VBA Express forum doesn't seem to work right in Excel 2010.

All of that said, things are mostly working, just not totally right. I'm trying to use these lockdowns for a spreadsheet calculation we are going to validate for use in a pharmaceutical QC lab so it has to be rugged.