VBA References

  • Thread starter Thread starter Tommy T
  • Start date Start date
T

Tommy T

I have a workbook that references an add-in. I am trying
to close the add-in when I close the workbook, but I
can't get it to work. I just get the message that "This
workbook is currently referenced by another workbook and
cannot be closed." I've tried putting the code in the
BeforeClose event, but since the workbook is technically
not closed yet, it still doesn't work.

Any Suggestions?
TT
 
TT,

You could do something like:

With ThisWorkbook.VBProject
.References.Remove .References("MyVBAProject")
End With
MyVBAProject.ThisWorkbook.Close

Be sure not to save changes to your own workbook, else the reference is gone
for good!

Rob
 
That is correct. You can't close a workbook that is referenced by another
workbook.

I would suggest removing the reference and closing the addin, but I don't
think the reference would actually be removed until the macro terminated.
 
Thanks Rob. Is there a way I can bring up the save
prompt (not the saveas dialog box) before I do this?
That way, I could have the user choose to save their
changes, but I would then set the displayalerts property
to false before I removed the reference.
 
Why make it complex, create the reference in the workbook_Open event.
Remove it in the beforeclose event. In either case, you can check if it
exists before doing anything.

Excel will bring up the save prompt and if you do the above, that will be
appropriate.
 
You could set the Saved property to True in the BeforeClose event, that way
Excel doesn't complain.

Otherwise I think should do it:
If MsgBox("Save Changes?", vbYesNo) = vbYes Then ThisWorkbook.Save

Rob
 
Back
Top