Disable/Enable Workbook Close

[avd88]

Hello,

I added an activeX button in my workbook to do some stuff and then save and close the file.
Need to disable closing the excel document with the x button or the "file - close" option.

I found some code online that makes sense but because I'm using a boolean variable on workbook I can't access and change that variable within my activeX button close.

So the code would be something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     If Not CloseMode Then
        Cancel = True
        MsgBox "Please use the Close File Button to close the file."
    End If
End Sub

Then the code inside the button would be something like this:

Private Sub ENDCloseButton_Click()
    'UNFILTER
    Dim ws As Worksheet
   
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible Then ws.ShowAllData
    Next ws
    On Error GoTo 0
   
    'UNPROTECT
        Sheets("IDN").Unprotect
        Sheets("BT").Unprotect
        Sheets("NPI").Unprotect
        Sheets("END").Unprotect
   
    'SAVE AND CLOSE
     CloseMode = True
     ThisWorkbook.Save
     Application.Quit
End Sub

In theory once CloseMode being equal to True would render the If statement on workbook_BeforeClose false and prevent the disabling but it is not doing that. I'm assuming the issue comes from using the variable in two different Subs. I tried defining the variable as a global variable but that didn't work.

Any ideas?

Thanks!

 

[smozgur]

Did you use the following declaration in one of the module's declaration sections (not in a sub and as Public)?

Public CloseMode As Boolean

 

[avd88]

Hi, yes I tried it without the Public keyword and just tried it with the Public keyword but I still can't get it to override that code:

 

[smozgur]

You need to declare it once. Remove one of the declarations and retry.

 

[avd88]

I did try declaring it once in the workbook module, then once in the button module and then in both and it did not work.
This shouldn't be that complicated and I've searched online and can't find a solution to this. There was a similar question asked here on Dec 2020 without a solution.

 

[smozgur]

I did mention that it should be declared in the "module", but I should have also said that it shouldn't be in the Class Module (ThisWorkbook class module in this case).

The following is the ThisWorkbook class module content - your code except for no variable declaration and Option Explicit statement.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     If Not CloseMode Then
        Cancel = True
        MsgBox "Please use the Close File Button to close the file."
    End If
End Sub

And the following is a standard module that contains the macro the button is assigned:

Option Explicit
Public CloseMode As Boolean

Private Sub ENDCloseButton_Click()
    ' other code here
     CloseMode = True
     ThisWorkbook.Save
     Application.Quit
End Sub

This is how it works, and I actually tested it even I am pretty sure how it works.

Please test it in a brand new workbook that doesn't contain anything else, and then you will see how it works.
It should but if it still doesn't work in your workbook after setting it as shown above, then there must be something else interfering.

Hint: Use Option Explicit statement as shown in my samples to catch if the variable is not known by either module.