1

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.

VBA Noob
  • 11
  • 4
  • It happens when you select Yes on the messagebox? – cyboashu Dec 20 '16 at 16:57
  • When I select No on my custom messagebox, the sub does what it's supposed to. After completion, it then pops up the default "Do you want to save changes" box. Clicking Save on THAT one then closes the workbook without firing the beforeclose event. I thought that setting "Activeworkbook.saved = true" would suppress the 2nd message box, but it doesn't. – VBA Noob Dec 20 '16 at 17:00
  • Not the answer you were hoping to find, but glad you were able to identify the root cause. Cheers. – David Zemens Dec 20 '16 at 18:02

2 Answers2

1

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
cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • I tried your suggestion and it's still showing the second dialogue box. – VBA Noob Dec 20 '16 at 17:02
  • This seems to be working for me using either of `ThisWorkbook` or `Me` keyword. – David Zemens Dec 20 '16 at 17:04
  • 1
    Yeah for me too. VBA Noob , do you have any code on the other events such as BeforeSave? – cyboashu Dec 20 '16 at 17:05
  • The only other workbook level sub I have is Private Sub Workbook_Open() Call PullData End Sub – VBA Noob Dec 20 '16 at 17:06
  • 1
    Perhaps it's an issue with a company-specific addon. Even this still prompts the save box which will exit the workbook: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = true end sub – VBA Noob Dec 20 '16 at 17:09
  • 2
    The `Cancel` parameter in `Workbook_BeforeClose` event doesn't suppress the "Save" dialog. It provides a means by which you can abort the `Close` operation. To suppress the "Save" dialog, you need to do that using the workbook's `.Saved` property per @cyboashu answer. This tricks Excel in to treating the workbook as though it is saved, even if it is not. – David Zemens Dec 20 '16 at 17:15
  • I've tried Me.Saved=True, ThisWorkbook.Saved=True and ActiveWorkbook.Saved = True. None of those are preventing the save dialog box. – VBA Noob Dec 20 '16 at 17:17
0

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
Patrick Lepelletier
  • 24
  • 3