Prompt to Save Addins

Prompt to Save Addin

I’m pretty good about saving my work, and probably hit Ctrl-S a couple hundred times a day. And of course, as long as things don’t crash, Excel makes it hard to lose your work. One exception is addins, which don’t trigger a save prompt when you close them after making changes, at least when the IsAddin property is True. So I have a routine in my most-used addins that reminds me to save them. But I don’t have it in all of them. The other day this bit me, and I lost 10 minutes of work on an xlam. I decided to generalize my prompt to save addins and put it in an application-level event in my main utility addin. (These decisions come easy; what’s more fun than building a new tool?) This way I’m prompted to save any time I close an addin that I’ve changed.

If you’ve never used application-level events, Chip Pearson’s site has some good information. Okay, here’s how you can add this code to your favorite utility addin (personal.xls will do nicely).

Create a Class called “clsApplication” and paste this code into it:

Public WithEvents app As Excel.Application

Private Sub App_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As Boolean)
If wb.IsAddin And Not wb.Saved Then
    If MsgBox(wb.Name & "Addin" & vbCrLf & "is unsaved. Save?", _
              vbExclamation + vbYesNo, "Unsaved Addin") = vbYes Then
        If ExcelInstanceCount > 1 Then
            MsgBox "More than one Excel instance running." & vbCrLf & _
               "Save cancelled", _
                vbInformation, "Sorry"
           Exit Sub
        Else
            wb.Save
        End If
    End If
End If
End Sub

Create a global variable to hold the class instance. At the top of a regular code module (before any procedures) put this line:

Public cApplication As clsApplication

(I like to put all my global variables like the one above in a single module, called modGlobals.)

In the ThisWorkbook WorkbookOpen event for your utility addin, put this code:

Set cApplication = New clsApplication
Set cApplication.app = Excel.Application

One problem is that when an addin is saved with more than one instance of Excel open, it gets saved to a new location (maybe the folder of ActiveWorkbook?). So I added code to the BeforeClose event to cancel the save if that’s true. Here’s the function that does the checking:

Function GetExcelInstanceCount() As Long
Dim hwnd As Long
Dim i As Long
Do
    hwnd = FindWindowEx(0&, hwnd, "XLMAIN", vbNullString)
    i = i + 1
Loop Until hwnd = 0
GetExcelInstanceCount = i - 1
End Function

One last thing to do is add code to my global error handler that re-instantiates the cApplication.Class and its App property if they’ve gotten lost, which can easily happen during debugging.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>