How to detect "AfterXX" events (e.g. Workbook"After"Save)

[JackCopper]

I need to know when a workbook has been saved for certain (i.e., user did not cancel the SaveAs dialog). I would like to determine this through an event of some kind, but I have not found anything suitable.

I also have a similar problem with auto_close (in a module). If a user closes Excel using the close box, and there is an unsaved workbook, Excel will allow the user to cancel the close. But, auto_close has already been executed. So, where else can I put clean-up code that is ONLY executed when Excel really closes?

Thanks
Jack Copper

 

[rmikesmith]

Jack,

I'll answer your second question first. The following strategy is taken from John Walkenbach's book "Excel 2000 Power Programming With VBA". In order to take advantage of it, instead of using Auto_Close, you need to use Excel's new (starting with v. 97) Workbook_Close event procedure, located in the ThisWorkbook code module. Here is the how the event procedure should look:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim msg As String
Dim response As Integer

  If Not Me.Saved Then
    msg = "Do you want to save the changes you made to "
    msg = msg & Me.Name & "?"
    response = MsgBox(msg, vbInformation + vbYesNoCancel, "Microsoft Excel")
    Select Case response
    Case vbYes
      Me.Save
    Case vbNo
      Me.Saved = True
    Case vbCancel
      Cancel = True
      Exit Sub
    End Select
  End If
  ' Place your cleanup code here, or anything else you want
  ' to run when the workbook is actually closed.
End Sub

As to your first question, I'm not 100% sure what you're trying to do in this situation. However, you can use the ThisWorkbook.Saved property to determine if the workbook has been saved. This will be true in either of two situations: the user has saved the workbook, or no changes were made since it was opened.

HTH
M. Smith

 

[JackCopper]

Thanks for the suggestions, but unfortunately they don't quite address the problem.

My code is running as an add-in. The add-in explicitly loads a dll (for access to international message strings) during auto_open in a module in the xla add-in.

After Excel is merrily running, all GUI labels and messages come from the DLL. When Excel ends, I want to explicitly unload the DLL.

However, the event sequence when a user clicks the Excel close box is:

(the order of the first two may not be right, but they both occur BEFORE the user prompt)

auto_close (in the XLA main module, not a workbook)
[ThisWorkbook]BeforeClose
User prompt to save changed workbook [if an open workbook has been changed]

If the user elects NOT to end Excel, then my message DLL must remain loaded. Otherwise, I want to unload it. But I can't figure out how to cleanly (or any way!) determine in code (via some event) either that Excel is still alive (user decided not to end it) OR Excel is really ending (user decided to quit without saving).

With respect to my first question, again, I don't want to prompt the user in my .xla code, I just want to know as soon as the file has been saved (via SaveAs, thereby getting a new name/path) so that I can obtain the target directory path and use it to update another directory (all invisible to the user).

Any further suggestions or comments?
Thanks
Jack

 

[rmikesmith]

Jack,

I now understand what you're doing and the associated problem. I am going to propose a solution that I believe will work but may not be complete, meaning there may be ways to circumvent this (abeit inadvertently). I'll explain in a bit. Essentially, you will create a custom Exit procedure that will run when the user selects File -> Exit. The procedure checks each open workbook and determines whether it needs to be saved. If so, it prompts the user. If the user responds 'Yes', the workbook is saved; if 'No', the workbook's Saved property is set to true (to prevent Excel's built-in prompting to save); if 'Cancel', the procedure exits, taking no further action. In the case of the first two options (Yes/No) the cleanup code is run then Excel's Quit method is executed. This solution is not complete since the user could exit Excel using the X-box control or Close from the Control Menu. I will do some more digging for a better method. In the meantime here is the code:

Sub InstallCustomExit()
Dim ExitCtl As CommandBarControl

  Set ExitCtl = CommandBars("Worksheet Menu Bar").Controls("&File").Controls("E&xit")
  ExitCtl.OnAction = "CustomExit"

End Sub


Sub UnInstallCustomExit()
Dim ExitCtl As CommandBarControl

  Set ExitCtl = CommandBars("Worksheet Menu Bar").Controls("&File").Controls("E&xit")
  ExitCtl.OnAction = ""
End Sub


Sub CustomExit()
Dim Wkb As Workbook
Dim response As Integer
Dim msg As String

  For Each Wkb In Application.Workbooks
    If Not Wkb.Saved Then
      msg = "Do you want to save the changes you made to "
      msg = msg & Wkb.Name & "?"
      response = MsgBox(msg, vbInformation + vbYesNoCancel, "Microsoft Excel")
      Select Case response
      Case vbYes
        Wkb.Save
      Case vbNo
        Wkb.Saved = True
      Case vbCancel
        Exit Sub
      End Select
    End If
  Next Wkb
  
  ' **** Cleanup Code here ****
  Application.Quit
  
End Sub

The above 3 procedures should be placed into a standard Code Module.
In the ThisWorkbook code module of your addin, set up the following event procedures:

Private Sub Workbook_AddinInstall()
  InstallCustomExit
End Sub

Private Sub Workbook_AddinUninstall()
  UnInstallCustomExit
End Sub

Post back with your comments.

Mike

 

[JackCopper]

Though at first blush it seems a bit convoluted, I concede it also may be the only way - and certainly an option I hadn't thought about. I haven't had a chance to do any serious work on it yet - until an hour ago I thought I was going to China today. I'll try to do more later today (it's noon in my time zone) or this evening.

 

[rmikesmith]

Jack,

I am currently looking at using a Windows API function to disable/remove the Close item on the Excel System Menu, which would complement my previous code. I'll let you know what I come up with.

Regards,
Mike

 

[rmikesmith]

Jack,

The following procedures should complete the strategy presented in my previous post; namely, setting up a custom exit procedure while closing off alternate exit pathways the user might take. In particular, this requires disabling the Close item on the Excel System (Control) menu and disabling or assigning the custom exit procedure to the Alt-F4 keystroke command. To handle the former, I've used several Win32 API functions. I did run into a glitch trying to implement this; my intent was to simply disable the close menu item (w/ grayed out appearance). I could not get this function to work properly on the main Excel window (although it would work if the VB Editor was active). I will post this problem in the Win32API Forum. In the meantime, removing this menu selection (and the separator line) does the trick. In the case of the Alt-F4 shortcut, I just disable this, although you could assign the procedure that runs to "CustomExit". The following declarations should be placed in a standard code module. Calls to DisableSysMenuClose and DisableExitKey should be placed in the Workbook_AddinInstall() event procedure; place calls to EnableSysMenuClose and EnableExitKey in Workbook_AddinUnInstall() [both event procedures are located in the ThisWorkbook code module].

Public Const MF_BYPOSITION = &H400&
Public Const MF_GRAYED = &H1&
Public Const MF_REMOVE = &H1000

Public Declare Function GetActiveWindow Lib "user32" () As Long
Public Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Public Declare Function GetSystemMenu Lib "user32.dll" (ByVal hwnd As Long, ByVal bRevert _
    As Long) As Long
Public Declare Function GetMenuItemCount Lib "user32.dll" (ByVal hMenu As Long) As Long
Public Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long
Public Declare Function RemoveMenu Lib "user32" (ByVal hMenu As Long, _
     ByVal nPosition As Long, ByVal wFlags As Long) As Long


Sub DisableSysMenuClose()
Dim hwnd As Long
Dim hSysMenu As Long
Dim retVal As Long
Dim Count As Long
Dim wCaption As String

    wCaption = String$(256, 0)
    hwnd = GetActiveWindow
    retVal = GetWindowText(hwnd, wCaption, 255)
    wCaption = Left$(wCaption, retVal)
    If InStr(1, wCaption, "Microsoft Excel", vbTextCompare) = 0 Then
      Exit Sub
    End If
    hSysMenu = GetSystemMenu(hwnd, 0)
    Count = GetMenuItemCount(hSysMenu)
' The following call does not seem to work; see notes
'    Call EnableMenuItem(hSysMenu, Count - 1, MF_GRAYED Or MF_BYPOSITION)
' Instead, remove Close menu item + separator bar
    Call RemoveMenu(hSysMenu, Count - 1, MF_REMOVE Or MF_BYPOSITION)
    Call RemoveMenu(hSysMenu, Count - 2, MF_REMOVE Or MF_BYPOSITION)

End Sub


Sub EnableSysMenuClose()
Dim hwnd As Long
Dim hSysMenu As Long
Dim retVal As Long
Dim wCaption As String

    wCaption = String$(256, 0)
    hwnd = GetActiveWindow
    retVal = GetWindowText(hwnd, wCaption, 255)
    wCaption = Left$(wCaption, retVal)
    If InStr(1, wCaption, &quot;Microsoft Excel&quot;, vbTextCompare) <> 0 Then
      hSysMenu = GetSystemMenu(hwnd, True)
    End If

End Sub


Sub DisableExitKey()
  Application.OnKey key:=&quot;%{F4}&quot;, procedure:=&quot;&quot;
End Sub

Sub EnableExitKey()
  Application.OnKey key:=&quot;%{F4}&quot;
End Sub

Post back as to how you are getting along.

Mike

 

[rmikesmith]

Jack,

I forgot the obvious! You will also need to set up the following event procedures:

Private Sub Workbook_Open()
  InstallCustomExit
  DisableSysMenuClose
  DisableExitKey
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  UnInstallCustomExit
  EnableSysMenuClose
  EnableExitKey
End Sub

Mike

 

[rmikesmith]

John,

Haven't heard back from you. Does that mean you are in China?

Thanks to the help of experts in the VB/Win32API Forum, I have been able to modify the DisableSysMenuClose procedure to gray-out the Excel Close menu item rather than remove it completely. There are some additional constant and function declarations that should be placed at the top of your code module, as before. I am showing the complete DisableSysMenuClose function, which includes the modifications, but only the new API function/constants.

Option Explicit


Type MENUITEMINFO
  cbSize As Long
  fMask As Long
  fType As Long
  fState As Long
  wID As Long
  hSubMenu As Long
  hbmpChecked As Long
  hbmpUnchecked As Long
  dwItemData As Long
  dwTypeData As String
  cch As Long
End Type

'Menu item constants.
Const SC_CLOSE As Long = &HF060&
Const xSC_CLOSE As Long = -10

'SetMenuItemInfo fState constants.
Const MFS_GRAYED As Long = &H3&

'SetMenuItemInfo fMask constants.
Const MIIM_STATE As Long = &H1&
Const MIIM_ID As Long = &H2&

Public Declare Function SetMenuItemInfo Lib "user32" Alias _
    "SetMenuItemInfoA" (ByVal hMenu As Long, ByVal un As Long, _
    ByVal bool As Boolean, lpcMenuItemInfo As MENUITEMINFO) As Long


Sub DisableSysMenuClose()
Dim hwnd As Long
Dim hSysMenu As Long
Dim retVal As Long
Dim wCaption As String
Dim MI_Info As MENUITEMINFO

    wCaption = String$(256, 0)
    hwnd = GetActiveWindow
    retVal = GetWindowText(hwnd, wCaption, 255)
    wCaption = Left$(wCaption, retVal)
    If InStr(1, wCaption, "Microsoft Excel", vbTextCompare) = 0 Then
      Exit Sub
    End If
    hSysMenu = GetSystemMenu(hwnd, 0)
         
    With MI_Info
      .cbSize = Len(MI_Info)
      .fState = MFS_GRAYED
      .wID = xSC_CLOSE
      .fMask = MIIM_ID Or MIIM_STATE
    End With
    retVal = SetMenuItemInfo(hSysMenu, SC_CLOSE, False, MI_Info)

End Sub

Let me know if any of this helps.
Mike