Disable "X" on top right of excel

[Fwiz]

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

 

[Case_Germany]

Hi,

one of surely several possibilities:

Code in "ThisWorkbook"

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

Private Sub Workbook_Open()
    blnClose = False
End Sub

Code in a Module:

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]

Great this works... but I can't seem to the the workbook from the file close menu.

am I doing something wrong?

 

[davie1982]

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]

ADVERTISEMENT

Hi,

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

Code in ThisWorkbook:

Private Sub Workbook_Deactivate()
    OnOff True
End Sub

Private Sub Workbook_Open()
    OnOff False
End Sub

Code in a Module:

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]

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.

 

[Case_Germany]

Hi again,

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

I don't know. Here two examples - one for Excel 2007 and one for Excel 2003:

Not X 2003

Not X 2007

You can quit the worksheet with F5, a Button or a Clipart.

Case_Germany