How to disable "traditional" Save and Save As functionality

[JAMM302]

Hi - I need to disable "traditional" Save and Save As (via ribbon, Ctrl+S, File Menu) in my Excel 2016 workbook, but allow users to ONLY save my workbook with a macro enabled button. The macro enabled button logic works fine by itself, but i want to limit users' save capabilities to just that button.

Thanks!

[SamT]

If your macro uses the Save Dialog, it must set SaveByMarco = True before calling the Save Dialog

Option Explicit

Dim SaveByMacro As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If your macro uses the Save Dialog then use this version
If Not SaveByMacro Then
Cancel = True
MsgBox "Must use Macro to save"
Exit Sub
End If

SaveByMacro = False 'Reset after save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If your macro Does NOT use the Save Dialog then use this version
Cancel = True
MsgBox "Must use Macro to save"
Exit Sub
End sub

[JAMM302]

Thank you for your response and feedback. Should the logic above be included into the "ThisWorkbook" object? Also, here is what I had originally created in my Workbook, and which didn't work as expected...prompting me to ask the experts on this forum:


VBA to disable just the traditional "Save" functionality (saved in the "ThisWorkbook" object):



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


If SaveAsUI = False Then
Cancel = True
MsgBox "You must use the 'Save As' button on the Summary tab to save this pricing workbook", vbOKOnly + vbInformation, "Save Disabled"
End If


End Sub

Please see my reply in the post below for the macro I created for the "Save As" button:

[JAMM302]

VBA for the "Save As" macro-enabled button (saved in a VBA Module):



Sub SaveAsRoutine()


Dim OppNum As String
Dim ClientName As String
Dim UsrName As String
Dim sFileSaveName As Variant
Dim fileName As String


' Clear OppNum
OppNum = ""


'Set OppNum, ClientName, OppDate and User Name
UsrName = Environ("UserName")
OppNum = Range("C2").Value
ClientName = Range("C3").Value

' Insure OppNum is populated
If OppNum = "" Then
MsgBox "Please Enter an Opportunity Number"
Exit Sub
End If


'Set default SaveAs Path
Myroot = "C:\Users\" & UsrName & "\Documents\"


' Set filename equal to Opportunity Number, Client Name and Today's date
fileName = OppNum & " - " & ClientName & " Adhoc - " & Format(Now(), "MM-DD-YY")


' Save file in default location with custom file name
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=Myroot & fileName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName


End If


End Sub

[JAMM302]

The issue here is that the macro to disable the traditional "Save" worked. The button however (using the VBA above) returns the message and Save restrictions that I would expect from a Save...and not a Save As.


Thank you again for all of your help. Any advice to overcome these challenges is greatly appreciated!