Question About My Code Re. Disabling The Ability To Close Workbook With [X] Button

[Ark68]

I have this code in my workbook open module that disables the [X] button (close workbook) function.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Please use the application [EXIT] option to EXIT Excel.", vbInformation, "Sorry..."
End Sub

This does the trick.

My Exit button, which is the recommended way of closing the workbook looks like this:

Sub front_exit()
    Application.ScreenUpdating = False
    mbevents = False
    
    ui1 = MsgBox("Are you certain you wish to exit?", vbQuestion + vbYesNo, "Confirm exit")
    If ui1 = vbNo Then
        mbevents = True
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    'YES, exit
    'reset page
    With Worksheets("FRONT")
        .Unprotect
        .Range("E4") = "- Surname, Given -"
        .Range("E4:G4").Font.Italic = True
        .Range("E4:G4").Font.Size = 11
        .Range("E4:G4").Font.Color = RGB(0, 0, 0)
        
        .Range("E5") = "- Select -"
        .Range("E5:F5").Font.Italic = True
        .Range("E5:F5").Font.Size = 11
        .Range("E5:F5").Font.Color = RGB(0, 0, 0)
        .Protect
    End With
    'reset workbook
    wkbk_on_close
    
    'save and close workbook
    ui1 = MsgBox("Are you sure you want to SAVE and close this application?", vbQuestion + vbYesNo, "Confirm SAVE before close")
    If ui1 = vbYes Then
        MsgBox "Changes saved."
        ThisWorkbook.Close savechanges:=True
    Else
        MsgBox "No changes saved."
        ThisWorkbook.Close savechanges:=False  'enabled once app is working well. We don't want any user unauathorized changes to save
    End If
    
    mbevents = True
    Application.ScreenUpdating = True
    
End Sub

When the code in purple is executed, as it tries to close the workbook, the workbook close procedure kicks in naturally, and displays the message "Please use the application [EXIT] option to EXIT Excel." My question is, how can I avoid this instance of this message appearing?

 

[Micron]

One would be to have a module level variable (e.g. boolean) which front_exit sets it to true. In the workbook close event, if the value is true, don't show the message. With boolean you'd want to test for true as boolean defaults to false. Or you could use text or number variable.
I have to wonder why the bother. Just move the sheet editing code to the workbook close event because you can cancel it?
You're asking twice if user wants to close and save, plus having them click a resulting message box (3 message boxes). I have to think that will quickly become annoying for everyone.

 

[ZVI]

Hi.

Disable events triggering before saving code line and then restore it, like this:

Application.EnableEvents = False
ThisWorkbook.Close savechanges:=True
Application.EnableEvents = True

The same is for the ThisWorkbook.Close savechanges:=False

 

[Ark68]

Thank you Vladimir, that works great.

 

[ZVI]

Thank you Vladimir, that works great.

Glad it helped!

 

[Ark68]

Although ... I got myself in a pickle.
I can no longer get into the VBA editor. I have my taskbar and ribbon hidden, and when I "EXIT" the application closes. ALT-F11 doesn't work while the workbook is open.

 

[Ark68]

I have used this code in my workbook open code to hide the taskbar.

Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"

I figured if I created a keyboard shortcut, I could use it to reveal the taskbar when the shortcut is used. With the taskbar open, I can access VBA again.

Sub Reveal_Ribbon()
'
' Reveal_Ribbon Macro
' Reveal the ribbon
'
' Keyboard Shortcut: Ctrl+Shift+R
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
End Sub

But, similar to keyboard shortcut ALT-F11 for the VBA editor, this shortcut doesn't do anything either.

 

[ZVI]

Yea, I see - Application.ScreenUpdating = True will never happen because workbook is already closed before this code line.

Well, use this code in ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
  If IsOnExit Then Exit Sub ' <--- This works (updated code line)
 
  Cancel = True
 
  ' may be just call front_exit instead of the below warning
  MsgBox "Please use the application [EXIT] option to EXIT Excel.", vbInformation, "Sorry..."
 
End Sub

The code in the module for the [EXIT] button:

Option Explicit

Public IsOnExit As Boolean

Sub front_exit()
 
    Dim ui1
 
    ui1 = MsgBox("Are you certain you wish to exit?", vbQuestion + vbYesNo, "Confirm exit")
    If ui1 = vbNo Then
        IsOnExit = False
        Exit Sub
    End If
 
    'YES, exit
    Application.ScreenUpdating = False
 
    'reset page
    IsOnExit = True
    With Worksheets("FRONT")
        .Unprotect
        .Range("E4") = "- Surname, Given -"
        .Range("E4:G4").Font.Italic = True
        .Range("E4:G4").Font.Size = 11
        .Range("E4:G4").Font.Color = RGB(0, 0, 0)
   
        .Range("E5") = "- Select -"
        .Range("E5:F5").Font.Italic = True
        .Range("E5:F5").Font.Size = 11
        .Range("E5:F5").Font.Color = RGB(0, 0, 0)
        .Protect
    End With
 
    'reset workbook
    'wkbk_on_close <-- Uncomment this if needed
 
    Application.ScreenUpdating = True  ' <-- Do it before you close this workbook!
 
    'save and close workbook
    ui1 = MsgBox("Are you sure you want to SAVE and close this application?", vbQuestion + vbYesNo, "Confirm SAVE before close")
    If ui1 = vbYes Then
        'MsgBox "Changes saved."
        ThisWorkbook.Close savechanges:=True
    Else
        'MsgBox "No changes saved."
        ThisWorkbook.Close savechanges:=False  'enabled once app is working well. We don't want any user unauathorized changes to save
    End If
 
End Sub

 

[Ark68]

Thank you Vladimir! Should your lines Application.ScreenUpdating be Application.EnableEvents?

 

[ZVI]

Should your lines Application.ScreenUpdating be Application.EnableEvents?

My bad... use this code in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
  If IsOnExit Then Exit Sub ' <--- This works
 
  Cancel = True
 
  ' may be just call front_exit instead of the below warning
  MsgBox "Please use the application [EXIT] option to EXIT Excel.", vbInformation, "Sorry..."
 
End Sub

The Application.EnableEvents is redundant in this case, do not use it, as IsOnExit variable do the stuff