CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

Closing workbook, catch Cancel event

I have code for Workbook_BeforeClose event. I like how it works now but I have just noticed a problem with Application.Visible = False. When I click Yes, it saves Workbook, when I click No, it does nothing, but when I click Cancel it already done Application.Visible = False and I can't see Excel application. How to fix that? 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

        Application.Visible = False

        Application.DisplayFormulaBar = True
        ActiveWindow.DisplayHeadings = True
        ActiveWindow.DisplayGridlines = True

        ThisWorkbook.Unprotect Password:="123456"

        ActiveWorkbook.Sheets("Start").Visible = True

        ThisWorkbook.Worksheets("Start").Activate

        ThisWorkbook.Protect Password:="123456", Structure:=True, Windows:=False

  End Sub

Upvotes: 0

Views: 165

Answers (1)

Hans Olsson
Hans Olsson

Reputation: 55009

Instead of relying on the built-in dialog, try using your own. That way you have more control of what happens and when.

So maybe something like:

Dim closing As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not closing Then
        answer = MsgBox("Save data?", vbYesNoCancel + vbQuestion, "Save data?")

        If answer = vbYes Or answer = vbNo Then
            closing = True
            ' your code here
            ActiveWorkbook.Close savechanges:=answer = vbYes
        Else
            Cancel = True
        End If
    End If
End Sub

Upvotes: 1

Related Questions