CODEWITHSUNDEEP
vbaexcel
VBA Noob
VBA Noob

Reputation: 21

VBA Workbook_BeforeClose still prompts the Save window after setting Cancel to true. Clicking Save on that menu then closes the workbook

EDIT:

The issue is caused by a custom security addon that my company uses. Nothing I can do about it and is a very select problem, so I'm deleting the question so I don't confuse anyone who finds this in the future.

Upvotes: 2

Views: 1950

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 24

You might try to block before_save, telling him "i come from before_close" just by adding a module global boolean. If this boolean is true in before_save then cancel=true.

option explicit
Private BlockNormalSave as boolean

Private Sub Workbook_BeforeSave(Cancel As Boolean)
if BlockNormalSave then Cancel=true
end sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
BlockNormalSave=true
application.displayalerts=false 'not sure if prevents save window
thisworkbook.saved=true
'thisworkbook.close save:=false 'not sure either
end sub

Upvotes: 0

cyboashu
cyboashu

Reputation: 10433

Change ActiveWorkbook to ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ValidateData = True Then
        Call SendAndSave
    Else
        Select Case MsgBox("There are some invalid entries on the worksheet (values can only be between 0 and 5) so the changes were NOT " & _
            "sent to the server.  Do you still want to close the tool?", vbYesNo, "Warning")
        Case vbYes
            ThisWorkbook.Saved = True '/ won't ask the user to save
            ' but will still close.
        Case vbNo
            ThisWorkbook.Saved = True ''/ won't ask the user to save
            Cancel = True '/ Won't Close
        End Select
    End If

End Sub

Upvotes: 1

Related Questions