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?

 

[ZVI]

It is assumed that Public IsOnExit As Boolean variable is declared in standard code module (which is created via VBE-Insert-Nodule), not in the sheet's one

 

[Ark68]

I see. OK. With your suggested changes. In testing, from worksheet ws_front, i click the [X] button, I get the message to use the [EXIT] button on the worksheet (assigned macro front_exit).

The I click the [EXIT] button. Am I certain I want to exit? YES. I get an error "Unable to set the Italic property of the Font class" with the line highlighted in red in the front_exit code below.

If I stop the code from VBA Editor, and re-press the [EXIT] button again, answer YES to whether I am certain I want to exit, I get the message "Are you sure you want to save and close this application?". Whether I answer yes or no, the code executes as expected, however, I still get the "Please use the application [EXIT] option to EXIT Excel" associated with the workbook close code.

What is also unusual is when I answer YES to whether I want to exit - the second time around - it activates the last worksheet in my workbook. I don't see anywhere in my code where I wanted that.

Public IsOnExit As Boolean

Sub front_exit()
    
    'mbevents = False
   'Stop
    ui1 = MsgBox("Are you certain you wish to exit?", vbQuestion + vbYesNo, "Confirm exit")
    If ui1 = vbNo Then
        'mbevents = True
        InOnExit = 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
    
    Application.ScreenUpdating = True
    
    '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."
        'Application.EnableEvents = False
        ThisWorkbook.Close savechanges:=True
        'Application.EnableEvents = True
    Else
        MsgBox "No changes saved."
        'Application.EnableEvents = False
        ThisWorkbook.Close savechanges:=False  'enabled once app is working well. We don't want any user unauathorized changes to save
        'Application.EnableEvents = False
    End If
    
    'mbevents = True
    
End Sub

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

 

[ZVI]

Please use this updated code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
  If IsOnExit Then Exit Sub
 
  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

And let me know if it's ok.

 

[ZVI]

The code in the post #8 is updated now

 

[Ark68]

Very nice Vladimir. Seems to have done the trick. I had to keep the refewrences to mbevents. Without them I was getting the italic error. It prevented worksheet changes which when E4 changed would protect the worksheet. A protected worksheet prevented a change to E4.

But, is there a reason that you can figure out as to why I am unable to use any shortcuts. Without the taskbar, there is no way for me to access VBA Editor when I launch the workbook (my workbook open code "hides" the taskbar). Alt-F11, break, and any keyboard shortcut doesn't work. I have no way to access VBA Editor to access my code.

 

[ZVI]

... is there a reason that you can figure out as to why I am unable to use any shortcuts. Without the taskbar, there is no way for me to access VBA Editor when I launch the workbook (my workbook open code "hides" the taskbar). Alt-F11, break, and any keyboard shortcut doesn't work. I have no way to access VBA Editor to access my code.

How to reproduce this behavior?
After the Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)" all is working good on my side.

 

[ZVI]

Check if the Application.ScreenUpdating = True is applied at the end of your open code

 

[Ark68]

Hello again Vladimir. I am very appreciate of your continued support. It is very kind of you to continue to help me sort through these issues. As you can tell, I am a novice, and it's been so valuable learning from patient folk like yourself here. Thank you.

I added the line as suggested, but still access to ALT-F11.

Here is my open code:

Private Sub Workbook_Open()
    Dim xRet As Boolean
    Dim fname As String
    'Stop
    wkbk_on_open
    
    'Worksheets("GUI").Activate
    Call part1 '-> Definitions
    
    With Worksheets("FRONT")
        .Activate
        .Unprotect
        mbevents = False

        .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)

        .Range("H4") = ""
        .Range("G4:H4") = ""

        .Range("E4").Select
        .Protect
        mbevents = True
    End With
    Worksheets("FRONT").Activate
    Application.ScreenUpdating = True
    
    'NoSelect
    'Stop
End Sub

The procedure wkbk_on_open refers to this code. It's where the taskbar gets hidden.

Sub wkbk_on_open()
    Dim wnd As Window
    
    'hide menu/ribbon
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
    
    'hide formula bar
    Application.DisplayFormulaBar = False
    
    'hide gridlines
    
    For Each ws In Worksheets
        ws.Activate
        With ActiveWindow
            .DisplayHeadings = False
            .DisplayHorizontalScrollBar = False
            .DisplayVerticalScrollBar = False
            .DisplayWorkbookTabs = False
        End With
    Next ws
    
End Sub

 

[Ark68]

Turns out their was a conflict with shortcuts with Nvidia Experience. Once I got rid of it, I had Alt-F11 back with Excel.

 

[ZVI]

Turns out their was a conflict with shortcuts with Nvidia Experience. Once I got rid of it, I had Alt-F11 back with Excel.

Such a problem is not obvious... Congratulations, good job!