Disabling the Windows "Close" Button (The BIG RED 'X')

[LoneWolf3574]

I'm trying to figure out how to disable the Windows environment 'X', as that is a coworkers favorite way to close Excel he sometimes loses data because of it.

I use the following coding to prevent 'improper' closure of UserForms

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the 'Close' button!"
  End If
End Sub

I would like to know if there is a way to disable the 'X' that does not create a mess or cause issues in general? If it helps any, we are still using Windows XP Pro ver 2002 w/ SP3 with Office 2010, but we are slowly transitioning over to Win7 Pro, ours will be the LAST location about 6 months down the road.

[BeachRock]

Take a look at this.

http://www.excelforum.com/excel-prog...se-button.html

[LoneWolf3574]

It mostly works, but the problem I run into is that when the portion of my macro that closes Excel runs, the message box pops up again, preventing Excel from closing.

The code with the popup preventing Excel from closing using the windows 'X'

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
  MsgBox "Please select the 'Save & Close' button to close this program. Thank You."
  Cancel = True
End Sub

The code saving the workbook and trying to close Excel

Sub CloseWorkbooks()
'
' CloseWorkbooks Macro
'
    For Each w In Application.Workbooks
        w.Save
    Next w
    Application.Quit
End Sub

[john55]

I use a code provided by Leith Ross!
see if it helps you!

[LoneWolf3574]

I apologize for taking so long to respond on this, life in general ganged up on me real quick.

@ john55 - I couldn't get the solution you provided to work for me, maybe it's just my noobishness to VBA, I don't know.

I did find a solution that works best for me for another forums and feel that I should share in case anybody else wants to do this as well. The only real issue I have with it is that it must be activated every time the workbook is opened. So I just plug it into each module I want it in at the appropriate location and viola, it works for me.

Forum & Solution

Mind you, before the code, Sheet1 has a declaration of

Option Explicit

I'm still working on my understanding of all this, but I'm pretty sure that is needed to make the macro work

Option Explicit

Public Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Public Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As Long, ByVal uIDEnableItem As Long, ByVal uEnable As Long) As Long
'Used to find the Outlook icon in the system tray. If present then Outlook is running
Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Const MF_BYCOMMAND = &H0&
Public Const MF_BYPOSITION = &H400&
Public Const SC_ARRANGE = &HF110
Public Const SC_CLOSE = &HF060
Public Const SC_HOTKEY = &HF150
Public Const SC_HSCROLL = &HF080
Public Const SC_KEYMENU = &HF100
Public Const SC_MAXIMIZE = &HF030
Public Const SC_MINIMIZE = &HF020
Public Const SC_MOVE = &HF010
Public Const SC_NEXTWINDOW = &HF040
Public Const SC_PREVWINDOW = &HF050
Public Const SC_RESTORE = &HF120
Public Const SC_SIZE = &HF000
Public Const SC_VSCROLL = &HF070
Public Const SC_TASKLIST = &HF130
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2
Public Const HWND_TOP = 0
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOMOVE = &H2
Public Const GWL_STYLE = (-16)

Sub DisableExcelMenu()
' Remove Excel Items
Dim hMenu As Long
hMenu = GetSystemMenu(Application.hwnd, 0)
Call DeleteMenu(hMenu, SC_CLOSE, MF_BYCOMMAND)          ' Disable X (Close) Application button
Call DeleteMenu(hMenu, SC_MINIMIZE, MF_BYCOMMAND)       ' Disable Minimize application button
Call DeleteMenu(hMenu, SC_MAXIMIZE, MF_BYCOMMAND)       ' Disable Maximize application button
End Sub

Sub EnableExcelMenu()
' Restore Excel Items
Dim hMenu As Long
hMenu = GetSystemMenu(Application.hwnd, 1)
Call EnableMenuItem(hMenu, SC_CLOSE, MF_BYCOMMAND)      ' Enable X (Close) Application button
Call EnableMenuItem(hMenu, SC_MINIMIZE, MF_BYCOMMAND)   ' Enable Minimize application button
Call EnableMenuItem(hMenu, SC_MAXIMIZE, MF_BYCOMMAND)   ' Enable Maximize application button
End Sub

Thank you to those who took the time to try and help me, I greatly appreciate it.

If anybody has further suggestions, please do so within the forum guidelines.