Disable manual saving and only saving with pressing button

[slora00]

Hello people, I hope you're all doing great. I have this challenge that haven't been able to crack yet:

I need to disable or "restrict" users of a file to manually save the file (this includes using buttons pinned to the task bar, going to the "File" tab and clicking "Save" and "Save As" commands, and keyboard shortcuts as Ctrl + S).

The only way I want the users of the file to be able to save it is by pressing a button that will run a macro and format a few cells, and ultimately, save the file.

So I need help with a code that disables manual saving and all its' manifestations, and also, lets me run a code by pressing a button and save the file with THAT button.

Thank you in advance.

 

[Skyybot]

I have done this before. You have to completely re-write the XML that controls the Ribbon. You can even add your own save button. I found this useful.

How to Build a Custom Ribbon in Excel

Using XML and VBA

medium.com

 

[John_w]

In the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveButtonClicked Then
        Cancel = True
        MsgBox "You can only save this workbook by clicking the SAVE button on the sheet", vbExclamation
    End If
End Sub

In a standard module:

Public SaveButtonClicked As Boolean

Public Sub Save_Workbook()
    'Call your macro and format the cells here....

    SaveButtonClicked = True
    ThisWorkbook.Save
    SaveButtonClicked = False
    MsgBox "Workbook saved", vbInformation
End Sub

PS - Assign the Save_Workbook macro to your button.

 

[Skyybot]

In the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveButtonClicked Then
        Cancel = True
        MsgBox "You can only save this workbook by clicking the SAVE button on the sheet", vbExclamation
    End If
End Sub

In a standard module:

Public SaveButtonClicked As Boolean

Public Sub Save_Workbook()
    'Call your macro and format the cells here....

    SaveButtonClicked = True
    ThisWorkbook.Save
    SaveButtonClicked = False
    MsgBox "Workbook saved", vbInformation
End Sub

I tested the BeforeSave method and found that you could still save by clicking Save in the File menu. Or, the Save Icon in the Application Header.

 

[igold]

This works for me...
In the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    SaveAsUI = sv
    If SaveAsUI Then Cancel = True
End Sub

Private Sub Workbook_Open()
    sv = True
End Sub

In the Sheet module where your Button resides:

Private Sub CommandButton1_Click()
    Call cls
End Sub

In a standard module:

Option Explicit
Global sv As Boolean

Sub cls()
    sv = False
    ThisWorkbook.Save
    MsgBox "Your workbook is now saved and ready to be closed"
End Sub

 

[John_w]

I tested the BeforeSave method and found that you could still save by clicking Save in the File menu. Or, the Save Icon in the Application Header.

The code works for me; it prevents me from saving the workbook using the following methods:

• Disc icon on the Quick Access Toolbar
• File -> Save
• File -> Save As
• Ctrl+S

The crucial variable is the SaveButtonClicked flag.

 

[slora00]

This works for me...
In the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    SaveAsUI = sv
    If SaveAsUI Then Cancel = True
End Sub

Private Sub Workbook_Open()
    sv = True
End Sub

In the Sheet module where your Button resides:

Private Sub CommandButton1_Click()
    Call cls
End Sub

In a standard module:

Option Explicit
Global sv As Boolean

Sub cls()
    sv = False
    ThisWorkbook.Save
    MsgBox "Your workbook is now saved and ready to be closed"
End Sub

This worked perfectly man! Thank youuuu

 

[igold]

You're welcome, I was happy to help as I am sure @John_w and @Skyybot were as well. Thanks for the feedback!!