BeforeClose event

Magrão

New Member
Joined
Jan 26, 2009
Messages
13
Hi,

Does anyone know if there's a BeforeClose Application event, rather than a BeforeClose Workbook event? Or some way to disable the main Application Close button when more than one workbook is open?

I'm having a problem with some workbooks that rely heavily on event macros. The workbooks are, essentially, identical, with the same macros - just different data. If I have one workbook open, I can close it fine by using either the main Close button, or by using the Close Window button. If I have more than one workbook open I can also close fine using the Close Window button. However, with more than one workbook open, using the main 'Close' button, to close the whole application (i.e. all open workbooks), it causes some of my events subs to error.

The problem isn't so much with my coding (though I'm sure it could be vastly improved) but more with the sequence of events that are fired in this specific scenario. Hence my first question, about effectively disabling the application close event.

I might be able to code my way around this, but it's proving to be difficult (I've tried disabling event handling, btw - and it doesn't solve the problem).

Thanks for any suggestions.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,842
Office Version
2016
Platform
Windows
Hi.

This is not an easy task as there is no direct way to determine how a workbook is being closed ie: only the workbook is closing or the whole application.

What version of excel are you using ?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You can disable the X itself using code like this:
Code:
Private Declare Function GetSystemMenu Lib "User32" _
                                     (ByVal hwnd As Long, _
                                     ByVal bRevert As Long) As Long
      
Private Declare Function RemoveMenu Lib "User32" _
                                         (ByVal hwnd As Long, _
                                         ByVal nPosition As Long, _
                                         ByVal wFlags As Long) As Long
  
Private Declare Function DrawMenuBar Lib "User32" _
         (ByVal hwnd As Long) As Long
Private Const SC_CLOSE = &HF060
Private Const MF_BYCOMMAND = &H0
Public Sub DisableWinClose()
     Dim hSysMnu As Long
     hSysMnu = GetSystemMenu(Application.hwnd, 0)
     RemoveMenu hSysMnu, SC_CLOSE, MF_BYCOMMAND
     DrawMenuBar Application.hwnd
 End Sub
Public Sub EnableWinClose()
     Dim hSysMnu As Long
     hSysMnu = GetSystemMenu(Application.hwnd, True)
     DrawMenuBar Application.hwnd
End Sub
 

Magrão

New Member
Joined
Jan 26, 2009
Messages
13
Thanks Rory, that's the kind of thing I was after. I'll give this a try tomorrow. Cheers :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,121
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top