Changes to an add-in will not prompt for saving when closing the add-in.

Add this event code to add-in's Thisworkbook module.

Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
If Not Me.Saved Then
msg = "Do You Want Save Changes to "
msg = msg & Me.Name & "?"
ans = MsgBox(msg, vbQuestion + vbYesNoCancel)
Select Case ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
'Delete_Controls 'runs macro to delete controls
End With
End Sub

Note...........if addin has workbook_open code to add controls to menus or
toolbars you must add some code to delete those controls.


Gord Dibben MS Excel MVP


On Sun, 17 Oct 2010 21:26:37 -0400, "CompleteNewb"
wrote:

I'm wondering if this is a bug, or if I should have known this; I just
verified that when I open an .xla file (not adding it as an addin using
Excel, just doubleclicking right on it to open it) and make changes in the
VBA editor, when I close it it doesn't prompt to save changes, and it
doesn't save the changes. I have to remember to save.

This ALSO happens when I have it in the workbook as an Add-In. if I make
changes in the vba editor, then close the editor, no prompt. Then saving the
workbook doesn't save the vba changes either.

I found this out by losing about 15 hours of work. I know I'm not a
high-level programmer, but I thought I was beyond this kind of mistake.
None of my code has anything about turning off warnings or save prompts, I
had one bit that turned off screenupdating, but even if I interruped some
code, that being off wouldn't losr my save promots, right? I get save
prompts when I close a Workbook.

Is this by design? That I can work for 15 hours in the Excel vba editor,
and get no save and no prompt to save when I close it?