CODEWITHSUNDEEP
vbaexcel
user1452091
user1452091

Reputation: 123

How to disable the save function

I currently have a macro which does data mining and saves the workbook in the end. I intend to disable to the save function of the workbook and force the user to use the macro everytime the workbook needs to be saved. This is what I have so far but it does not seem to work. When I do this, my macro and this sub described below are both running in a loop. every time my macro tries to save the workbook, this sub is not allowing it. I basically want to force the user to use the macro to save the workbook.

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

    NoSave = MsgBox("Changes have to be submitted before the workbook can be saved, Proceed and submit ?", vbYesNo, "Continue?")

    If NoSave = vbNo Then
        Cancel = True
    Else
        Main
    End If
End Sub

Upvotes: 3

Views: 4703

Answers (2)

Trace
Trace

Reputation: 18899

Alternative, how about this (I misunderstood the question at first, but also wanted to give it a try since it's interesting):
Declare public boolean (exceptional) in the thisworkbook module: 

Option Explicit
Public bSave As Boolean

In the event BeforeSave event: 

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

Dim sNoSave As String

If bSave = True Then
    bSave = False
    Exit Sub
End If

sNoSave = MsgBox("Changes have to be submitted before the workbook can be saved, Proceed and submit ?", vbYesNo, "Continue?")

If sNoSave = vbNo Then
    bSave = False
    Cancel = True
    Exit Sub
Else
    bSave = True
    Call Main(bSave)
End If

End Sub

In Main:

Option Explicit

Sub Main(bSave)

If bSave = True Then
    ThisWorkbook.SaveAs Filename:="U:\Book1.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    MsgBox "Main method called"
End If

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

Here is an Example. Paste this in ThisWorkbook. This will not let you use the Save or the SaveAs. You can however use the macro SaveThisFile to save the workbook. Please amend it to suit your needs.

Option Explicit

Dim SaveByCode As Boolean
Const msg As String = "Please use the macro to save the file"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Saved = False And SaveByCode = False Then
        MsgBox msg, vbExclamation, "Unable to save"
        Cancel = True
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    If SaveByCode = True Then
        SaveThisFile
    Else
        MsgBox msg, vbExclamation, "Unable to save"
        Cancel = True
    End If
    Application.EnableEvents = True
End Sub

'~~> Your macro to save the file
Sub SaveThisFile()
    SaveByCode = True
    ThisWorkbook.Save
End Sub

NOTE: If your Save macro is in a module then remove this Dim SaveByCode As Boolean from ThisWorkbook and place Public SaveByCode As Boolean in a module.

Upvotes: 2

Related Questions