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


"Tommy T" wrote in message
...
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

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.


-----Original Message-----
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


"Tommy T" wrote in
message
...
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


.

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.

--
Regards,
Tom Ogilvy

Tommy Y wrote in message
...
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.


-----Original Message-----
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


"Tommy T" wrote in
message
...
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


.

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


"Tommy Y" wrote in message
...
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.


-----Original Message-----
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


"Tommy T" wrote in
message
...
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


.

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.

--
Regards,
Tom Ogilvy

Tommy T wrote in message
...
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