Run macro before closing Excel 2007

L

Lasse

Hi

I can read a lot of posts regarding Workbook_BeforeClose but can't quit find
the answer to problem.
When using Workbook_BeforeClose it's run when closing the active worksheet
and leaves Excel running without any open worksheets. I need a macro that
runs when the user closes Excel not just the worksheet.
I run a macro that installs a add-in through Workbook_Open and I want to
remove the add-in when the user closes Excel?

/Lasse
 
T

The Code Cage Team

Workbook_BeforeClose event only runs when the workbook is closed, yo
cannot "close" the activesheet unless changing to another, it's a bi
harsh but you can use Application.Quit which closes the whol
application.

What is it that you are running that wont allow the shell of excel t
close? can you supply the code from your Workbook_BeforeClose

--
The Code Cage Tea

Regards,
The Code Cage Team
www.thecodecage.co
 
L

Lasse

Hi

I have a Workbook_Open() in Personal.xlsb where I run the following code:
Set MyAddIn = AddIns.Add(Filename:="C:\Excel\addin.xlam", CopyFile:=True)
AddIns("AddIn_Name").Installed = True

I would like the add-in to get removed when closing Excel completely and not
just when closing a worksheet. I need the add-in to be accessible as long as
the user has Excel running.

I would like the following macro to run when Excel is closed:
AddIns("AddIn_Name").Installed = False

The reason is that if the addin.xlam file is missing for some reason I do a
check in Workbook_Open that copies it from the network, this works fine but
Excel returns a error messeage when opening and the .xlam file is missing. So
my idea was to unload the add-in before exit each time so that Excel won't
complaint if the file is missing.

I hope this makes sense :)

/Lasse
 
T

The Code Cage Team

Perhaps something like:
If AddIns("YOUR Add-in").Installed = True Then
AddIns("YOUR Add-in").Installed = False
End I

--
The Code Cage Tea

Regards,
The Code Cage Team
www.thecodecage.co
 
L

Lasse

Hi again

I have tried the code you suggest but if I put it in Workbook_BeforeClose it
will remove the add-in if just the active workbook is closed, I only want it
to remove the add-in if Excel is closed.
If a user opens a workbook and then wants to close it without closing Excel
so he/she can open a new workbook the add-in has to be present.

/Lasse
 
T

The Code Cage Team

I kind of understand what you are getting at but if you have the code in
the PERSONAL.xlsm Workbooks_Open event then EVERY workbook that is
opened should have the add-in installed regardless of whether a
Workbook_BeforeClose has just uninstalled it because a workbook hase
been closed!

Does the add-in really have to be uninstalled?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
 
L

Lasse

So far thanks for your input, it's greatly appreciated! Maybe I should get a
profile on your site :)

The only reason I want to uninstall the add-in is to avoid getting a
build-in error message from Excel if the add-in.xlam file is missing when
opening Excel. The build-in message appears before Excel runs the content of
ThisWorkbook so I assume that I can't suppress it.

I have just tried adding the following to ThisWorkbook in Personal.xlsb:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Add-in").installed = False
Msgbox "Add-in uninstalled"
End Sub

But it never executes it when closing a worksheet og Excel?

/Lasse
 
T

The Code Cage Team

Yes feel free to join our site!
You will have to use error trapping to handle the error as it occurs
i'll take a look and get back to you ;

--
The Code Cage Tea

Regards,
The Code Cage Team
www.thecodecage.co
 
H

Harald Staff

Lasse

Turn your Personal.xlsb into MyAddin.xlam, and install it, for the desired
behavior.

HTH. Best wishes Harald
 
Top