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

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code in my workbook open module that disables the [X] button (close workbook) function.

Code: 
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:

Rich (BB code): 
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?
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

Rich (BB code): 
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

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = Not IsOnExit
MsgBox "Please use the application [EXIT] option to EXIT Excel.", vbInformation, "Sorry..."
End Sub
 
Upvote 0
Please use this updated code:
VBA 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.
 
Last edited:
Upvote 0
Solution
The code in the post #8 is updated now
 
Last edited:
Upvote 0
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.
 
Upvote 0
Ark68 said:
... 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.
Click to expand...
How to reproduce this behavior?
After the Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)" all is working good on my side.
 
Upvote 0
Check if the Application.ScreenUpdating = True is applied at the end of your open code
 
Upvote 0
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:
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.
Code: 
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
 
Upvote 0
Turns out their was a conflict with shortcuts with Nvidia Experience. Once I got rid of it, I had Alt-F11 back with Excel.
 
Upvote 0
Ark68 said:
Turns out their was a conflict with shortcuts with Nvidia Experience. Once I got rid of it, I had Alt-F11 back with Excel.
Click to expand...
Such a problem is not obvious... Congratulations, good job! (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Disable 'X' on Worksheet
Replies
4
Views
117
Pacman52
P
V
  • Solved
Open Workbook, save as a new file, work in the new file, close the original Workbook
Replies
3
Views
134
dmt32
D
P
  • Question Question
Emailing 1 sheet
Replies
6
Views
255
Micron
M
C
  • Solved
Message box appearing twice when closing workbook
Replies
3
Views
490
Alex Blakenburg
A
W
  • Solved
Modify Column format before saving as csv file
2
Replies
12
Views
943
WascoWarrior
W

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top