BeforeClose event

[Magrão]

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.

 

[Jaafar Tribak]

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 ?

 

[Magrão]

I'm using good old-fashioned 2003

 

[Magrão]

Bump! Can anyone help with this?

 

[RoryA]

You can disable the X itself using code like this:

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]

Thanks Rory, that's the kind of thing I was after. I'll give this a try tomorrow. Cheers