CODEWITHSUNDEEP
excelvba
Akanksha
Akanksha

Reputation: 21

Cancel = True is not working in Workbook_BeforeClose

Debug Mode, showing that value of <code>Cancel</code> is set to <code>True</code>

This clearly shows that value of cancel is true, and still my workbook closes after this! Why?

I want to cancel closing of the excel file if user input is incorrect.

I debug the code and found out that the value of Cancel was initially False, and it becomes True. But once the Sub ends, file is still closed.

(variable error is defined at workbook declaration space, so accessible within module.)

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        error = 0

        check_for_error
        If error = 1 Then
            Cancel = True
            Exit Sub
        End If

        Application.EnableEvents = False
        Sheet1.Cells.ClearContents
        Sheet2.Cells.Clear
        Application.EnableEvents = True
    End Sub

I want to cancel closing of file if error = 1.

Update:: Thanks everyone for the responses, but nothing seems to work for me! Following is how my current code looks.

Option Explicit
Dim errYes As Byte

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    check_freeze_panel
    If errYes = 1 Then
        Cancel = True
        Exit Sub
    End If

    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
End Sub

errYes is returning 1, Cancel = True is being executed, and still my workbook closes.

I even tried commenting all the code, and just putting Cancel = True as suggested and guess what, it still closes!

Upvotes: 1

Views: 2472

Answers (3)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Change the name of error this is a reserved word for an already existing function (see Error function) and must not be used as a variable name. And activate Option Explicit and declare all your variables.

Here is an example how you would do it correctly:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ErrorsFound As Boolean
    ErrorsFound = check_for_error

    If ErrorsFound Then
        Cancel = True
        Exit Sub
    End If

    Application.EnableEvents = False
    Sheet1.Cells.ClearContents
    Sheet2.Cells.Clear
    Application.EnableEvents = True
End Sub

Function check_for_error() As Boolean
    'check and return
    check_for_error = True 'errors found
End Function

Or even shorter:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = check_for_error

    If Not Cancel Then
        Application.EnableEvents = False
        Sheet1.Cells.ClearContents
        Sheet2.Cells.Clear
        Application.EnableEvents = True
    End If
End Sub

Function check_for_error() As Boolean
    'check and return
    check_for_error = True 'errors found
End Function

Upvotes: 3

cekar
cekar

Reputation: 366

Initially Cancel is equal False, means File Close will succeed. For me the below code works fine. When I set error = 1 then closing is cancelled whether you use "Exit Sub" or not. When I set error <> 1 then cls When I test your Code:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim error As Integer
error = 1

If error = 1 Then
    Cancel = True

    'Exit Sub
End If

End Sub

Just test this: Write False and workbook should Close, and True should "stop" the closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Cancel = False

End Sub

Upvotes: 0

Brian
Brian

Reputation: 2108

Try this. Set Error equal to your function call and make sure it returns the right data type.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    error = check_for_error

    If error = 1 Then
        Cancel = True
        Exit Sub
    End If

    Application.EnableEvents = False
    Sheet1.Cells.ClearContents
    Sheet2.Cells.Clear
    Application.EnableEvents = True
End Sub

Upvotes: 0

Related Questions