Disable "X" on top right of excel

Fwiz

Board Regular
Joined
May 15, 2007
Messages
238
As we all know, by clicking X on the right corner of the spreadsheet or the X on top corner on excel closes with workbook or excel,

Is there a way to disable this function?

thanks
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Case_Germany

Active Member
Joined
May 13, 2008
Messages
408
Hi,

one of surely several possibilities:

Code in "ThisWorkbook"

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not blnClose
End Sub

Private Sub Workbook_Open()
    blnClose = False
End Sub
Code in a Module:

Code:
Public blnClose  As Boolean

Public Sub Close_Code()
    blnClose = True
    ThisWorkbook.Close
End Sub
The Sub "Close_Code()" assign a button at Excel to close.

Case_Germany
 

Fwiz

Board Regular
Joined
May 15, 2007
Messages
238
Great this works... but I can't seem to the the workbook from the file close menu.

am I doing something wrong?
 

davie1982

Board Regular
Joined
Nov 19, 2007
Messages
170
Office Version
  1. 365
  2. 2019
You need to create a button on the menu or create a form button to close the workbook..

tools - customize

Put a button up there

assign the macro Close_Code to the button

You should be able to close after clicking that.
 

Case_Germany

Active Member
Joined
May 13, 2008
Messages
408

ADVERTISEMENT

Hi,

another possibility is API for the X that close the application (but that won't work in Excel 2007):

Code in ThisWorkbook:

Code:
Private Sub Workbook_Deactivate()
    OnOff True
End Sub

Private Sub Workbook_Open()
    OnOff False
End Sub
Code in a Module:

Code:
Private Declare Function DeleteMenu Lib "user32.dll" ( _
    ByVal hMenu As Long, _
    ByVal nPosition As Long, _
    ByVal wFlags As Long) As Long
Private Declare Function DrawMenuBar Lib "user32.dll" ( _
    ByVal hwnd As Long) As Long
Private Declare Function GetSystemMenu Lib "user32.dll" ( _
    ByVal hwnd As Long, _
    ByVal bRevert As Long) As Long
     
Private Const MF_BYCOMMAND = &H0
Private Const SC_CLOSE = &HF060

Sub OnOff(blnOn As Boolean)
    Dim hWndMenu As Long
    If blnOn = False Then
        hWndMenu = GetSystemMenu(Application.hwnd, 0&)
        If hWndMenu <> 0 Then
            DeleteMenu hWndMenu, SC_CLOSE, MF_BYCOMMAND
            DrawMenuBar Application.hwnd
        End If
    Else
        hWndMenu = GetSystemMenu(Application.hwnd, -1)
        DrawMenuBar hWndMenu
    End If
End Sub
For the X of the worksheet protect the workbook and pick "Windows".

Case_Germany
 

Fwiz

Board Regular
Joined
May 15, 2007
Messages
238
a Genius ....

Thanks the works, the original example you provided me worked a treat but didn't seem to allow me to close the file from file close menu - even though I added in a macro button toolbar.

am I being thick by not adding this code in the correct area?

added the code:

Private Sub Workbook_Deactivate()
OnOff True
End Sub

Private Sub Workbook_Open()
OnOff False
End Sub

in the "this workbook" and the

Public blnClose As Boolean

Public Sub Close_Code()
blnClose = True
ThisWorkbook.Close
End Sub

code under module 1 -

the 2nd version is equally good - but ideally would need this to be compatible with the excel 2007 in event of future updates.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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
Top