Use beforesave event to cancel save

[gschwint]

I have created a userform that captures via a public variable if a save of the workbook is desired or not. I attempt to use this public variable result within the workbook beforesave event to either save the workbook or cancel the save (exit the beforesave event). Here is the code that I think should work but it doesn't as the workbook is saved in all instances.

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

Proper_Save.Show 'call userform to get public variable "savequestion" result

If savequestion = True Then 'exit the default save and go back to the program
Cancel = True
Exit Sub
End If

End Sub

I have found that the public variable approach works good and is working properly by returning the correct result (true/false) but the workbook is saved regardless of the result. Any ideas???

 

[Jon von der Heyden]

I would like to see how (i.e. the code) the savequestion public variable is loaded.

 

[gschwint]

Here is the code behind the userform.

Private Sub CommandButton1_Click()

savequestion = True 'don't save

Proper_Save.Hide

End Sub

Private Sub CommandButton2_Click()

response = MsgBox("Are you sure that you want to continue to save in a manner other than what the programmer designer recommends?" & vbNewLine & vbNewLine & "Keep in mind that when the MMP program is changed or updated, this method of saving will require you to re-enter everything you have done to this point, in order to utilize the most current version of the MCPA MMP Spreadsheet!", vbYesNo, "You have been fairly warned!!!")

If response = vbYes Then 'save program
savequestion = False
Proper_Save.Hide
Else 'don't save
savequestion = True
Proper_Save.Hide
End If
End Sub

The variable declaration takes place in a seperate module but isn't used anywhere else. I try to keep my public variables in one location so I would like to leave it there, unless that is the issue. Here is the declaration:

Public admin, savequestion As Boolean

 

[Jon von der Heyden]

Well that works for me so it's hard to say but it does seem as though the variable is being destroyed (State Loss perhaps).

Have you tried using a property instead? You are hiding the form rather than unloading it (which is good because the class isn't terminated). So you could use something like:

In the userform class module:

Private m_blnSave As Boolean

Public Property Let SaveQuestion(ByVal blnSave As Boolean)
    m_blnSave = blnSave
End Property

Public Property Get SaveQuestion() As Boolean
    Me.Show
    SaveQuestion = m_blnSave
End Property

Private Sub CommandButton1_Click()
    With Me
        .SaveQuestion = True
        .Hide
    End With
End Sub

Private Sub CommandButton2_Click()
    response = MsgBox("Are you sure that you want to continue to save in a manner other than what the programmer designer recommends?" & vbNewLine & vbNewLine & "Keep in mind that when the MMP program is changed or updated, this method of saving will require you to re-enter everything you have done to this point, in order to utilize the most current version of the MCPA MMP Spreadsheet!", vbYesNo, "You have been fairly warned!!!")
    
    With Me
        If response = vbYes Then
            .SaveQuestion = False
        Else
            .SaveQuestion = True
        End If
        .Hide
    End With
End Sub

In ThisWorkbook class module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = Proper_Save.SaveQuestion
End Sub

 

[gschwint]

That still triggers a workbook save no matter the response. I just tried to use a much simplier approach to see if the beforesave event was actually working and it appears that it is not. I used a simple message box (see below) and when the answer is no, the save should cancel, however it does not. Any idea why I am experiencing this issue?

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

response = MsgBox("Are you sure you want to do this?", vbYesNo, "trial")

If response = vbNo Then Cancel = True

End Sub

I just tried this (above coed) on a new workbook using Excel 2007 on a Windows XP machine with no data at all and it did not work there either. It took me to the save-as window where I could then cancel the save. I then tried it using Excel 2000 on a XP machine and it worked just fine. Is it possible this is an issue with the 2007 version of Excel?

.

 

[Jon von der Heyden]

In the immediate window:

?Application.EnableEvents

What does this return?

I once had the SAPBEX addin running and it interfered with it. Are you running any addins?

 

[gschwint]

It returns "true"

The only add-ins I have running are those that I have no control over as they are auto loaded on startup at my employer. It is an application for saving info into our electronic records system called OnBase.

 

[Jon von der Heyden]

I believe one of those addins might be the culprit. I can't think of any other reason why...

 

[gschwint]

I'll see if I can get our IT people to allow me to remove that add-in and then give it a try. Thanks for the help.

 

[gschwint]

- UPDATE -

Problem Solved! The issue was the add-in that was runinng. After disabling it my original code worked just fine.