disable workbook close button

    hi guys,


    I found the following code in this forum, to disable the "X", workbook close button.

    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)          
If CloseMode = 0 Then Cancel = True
    MsgBox "Please go to File > Close to close this file" 
End Sub


    The thing is that when I click the "X",the message box appear, but as soon as I acknowledge the message, the book closes anyways. Please would someone have a look to see what is missing here?...Thanx to each and everyone...

    Re: disable workbook close button


    Unfortunately, that code is incorrect.


    As I mentioned, a workbook BeforeClose event does not have a CloseMode parameter. You have your VBA dev environment set to the default to allow non-declared variables to exist, so VBA simply 'creates' a variable called 'CloseMode' when the code runs.


    Suggest you go to the Tool/Options menu and on the Editor Tab, select the 'Require Variable Declaration' option. This does as it says - all variables must be declared before they are referenced in code. It might be a bit of a bind, but it will save you some day.


    Why do you want to disable the 'X' to close the workbook? The user can get the same effect by selecting the File/Close menu options and you haven't checked that.

    Re: disable workbook close button


    I agree, it's only really to guide the user to make use of the button given. If they really want to be otherwise, well, then they can but they will only shoot themselves in the foot, especially if they choose not to save, so to make sure all work is saved, the button had been provided. Another reason for providing the button is to make sure the book closes properly. For some reason if the "X" is used, the book closes as well as the application, HOWEVER, if I open the book again excel gives a message saying "Sorry, excel cannot open two workbooks with the same name"...I cannot, and have been battling for weeks to sort that out, but I just cannot pinpoint the source for this condition....The book is huge with about 15 sheets, and each one contains quite a bit of coding..

    Re: disable workbook close button


    Unfortunately, all of that points to bigger underlying problems with your code. You have some references that are not being released when the workbooks closes with the 'X' button.


    For example, if you close the workbook as you described, then go to Task Manger, you'll probably find Excel still listed in the running processes (or Background Processes, Win 8+). There's a difference between Applications and Processes. Applications are what the user sees, Processes are system processes and are not usually visible to the user. If Excel is held open by other references, then it will appear in the Processes list.


    If your workbook closes cleanly using the button (or whatever), then a workaround...

    • Declare a Public Variable in a module (must be in a module, not a userform or worksheet/workbook class module). Lets call it 'CloseMode'
      Code
      Public CloseMode as Boolean


    • In the Workbook_BeforeClose event handler, check the value of CloseMode and cancel the close as needed.

      Code
      Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '// Do not use the In-line If...Then statement here
    If Not CloseMode  Then 
        Cancel = True
        MsgBox "Please use the button to close this file"
    End If
End Sub


    • In the button click Event, set CloseMode = True and Save/Close the workbook. If the procedure is cancelled for any reason, then set CloseMode False before exiting the procedure.

    Re: disable workbook close button


    Thanks for sharing this
    I applied your steps
    now can't close the workbook ..It works with close button / File >> Close / File >> Exit / Alt + F4 / Even if right click in taskbar and Close window
    All the ways are closed ..Just one way from Task manager
    How could the workbook closed through line of code then?

    Re: disable workbook close button


    I didn't read the last line well
    I applied it and it is ok now

    Code
    Sub CloseMe()
    CloseMode = True
    ThisWorkbook.Save
    Application.Quit
End Sub



    EDIT:
    Regardless - please do not post questions in other members threads. If you have an issue, then start your own thread and add a link to the original thread if you think it can provide clarity or background information.

    Re: disable workbook close button


    When it comes to VBE, I'm clueless...really. I have put point 1 and 2 as you said. I do not know how to carry out point 3. The next code is the one in the button:

    Code
    Sub CloseWorkBook()
'
' CloseWorkBook Macro
'
'
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
End Sub


    Please help

    Re: disable workbook close button


    cytop,


    I saw what BaraaKhalil did..I know its exactly what you said, I was just to stupid to know what was meant by point 3. Thanx to you and also to Baraakhalil...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login