CODEWITHSUNDEEP
excelvba
user1556274
user1556274

Reputation: 37

How to prevent saving Excel workbook unless a command button is clicked?

I have a command button to save an Excel workbook in a particular format.

I want that the users are unable to save the workbook unless they click that command button.

To prevent the use of traditional saving options I used the code below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "You can't save this workbook!"
    Cancel = True
End Sub

How do I prevent traditional saving options but allow saving if the command button is clicked?

Upvotes: 0

Views: 1367

Answers (1)

41686d6564
41686d6564

Reputation: 19641

You can have a boolean flag to indicate whether or not saving is allowed and only set it to true when you call the macro that contains your logic for saving the workbook.

Add the following code under ThisWorkbook:

Public AllowSave As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not AllowSave Then
        MsgBox "You can't save this workbook!"
        Cancel = True
    End If
End Sub

Public Sub CustomSave()
    ThisWorkbook.AllowSave = True

    ' TODO: Add the custom save logic
    ThisWorkbook.SaveAs '....

    MsgBox "The workbook has been saved successfully.", , "Workbook saved"
    ThisWorkbook.AllowSave = False
End Sub

Note that you'll need to assign the CustomSave macro to the button that will be initiating the custom save.

Demo:

Demo

Upvotes: 3

Related Questions