Test If Add-In Workbook is being Referenced

[Ryan H]

I have an Add-In Workbook that contains userforms and code which is
referenced by other workbooks. This is my problem, I may have 4 workbooks
open referencing the add-in wbk. If all those workbooks close, but the Excel
application remains open, my add-in does not close. Is there a way to put
code in the add in wbk to check to see if any workbooks are referencing it
and if not close the add-in?

Thanks in Advance!

 

[Peter T]

AFAIK there is no direct way for an addin to know if all other workbooks
that reference it have closed (by reference I assume you mean UDFs, though
the same would apply if you meant tools/ref's).

If it's important to you, the open event of any workbook that references the
xla could increment some cell in the xla'a sheet. When the workbook closes
it could decrement the counter. A change event in the xla could close itself
if the counter reaches zero.

Personally I'd be inclined to allow the xla to stay open and not worry about
it!

Regards,
Peter T

 

[Jacob Skaria]

Try
Msgbox AddIns("analysis toolpak").Installed

'You can check for the other available Add-Ins
Sub Macro()
For Each myAddin In Application.AddIns
If myAddin.Name = "ATPVBAEN.XLA" Then
MsgBox myAddin.Installed
End If
Next
End Sub



If this post helps click Yes

 

[Jacob Skaria]

Ryan, have you tried this; if this does not work let me know...

If this post helps click Yes

 

[Ryan H]

Sorry for the slow response Jacob. I tried this code below and it doesn't
seem to find my add-in.

Sub FindAddIn()

Dim myAddin As Variant

For Each myAddin In Application.AddIns
If myAddin.Name = "QG Add-In.xla" Then
MsgBox myAddin.Installed
End If
Debug.Print myAddin.Name
Next

End Sub

This is what was printed in the Immediate Window:
ANALYS32.XLL
ATPVBAEN.XLA
SUMIF.XLA
EUROTOOL.XLA
EG2000.xla
HTML.XLA
LOOKUP.XLA
SOLVER.XLA

Let me explain exactly what I'm looking for maybe you can give me another
clue of what to do. I have a workbook named "QUOTE GENERATOR.xls". I added
a Reference workbook by selecting Tools>References> then selected "QG
Add-In.xla" in the VBA Editor. "QG Add-In.xla" contains all of the code and
userforms and "QUOTE GENERATOR.xls" stores data to be used in the userforms.
Sometimes there may be instances where "QUOTE GENERATOR.xls" is open multiple
times and all of those instances access the "QG Add-In.xla". The problem is
when all the copies of "QUOTE GENERATOR.xls" are closed, but Excel
Application is not closed the "QG Add-In.xla" remains open. I would like to
have code that can detect if any workbooks are referencing "QG Add-In.xla",
is that possible?