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:
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:
(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.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:
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.