VBA editor doesn't prompt to save changes, doesn't save

[CompleteNewb]

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?

 

[Gord Dibben]

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

 

[CompleteNewb]

Thanks, Gord.

I must say I'm very surprised not to have found any questions about this on
the web. It seems like very surprising beahviour, and I guess you need to be
kind of an insider to know it. I'm surprised no one's been on that cusp
when they're just making an add-in for the first time and has never been
surprised by this.

Thanks again.

 

[Gord Dibben]

I don't know if there is guide on add-ins with respect to "close without asking"

I believe I learned from experience as you have.

Generally when developing an add-in I work on a simple workbook to make all
changes to code and/or adding/ deleting modules.

If you close that workbook you will be asked to save changes.

When finished with your developing, save the simple workbook as an Add-in.

Later on if making major alterations that will involve time, don't forget that
you can change IsAddin from True to False whilst developing.


Gord