Is this why BeforeClose doesn't work in my Add-In? (long)

[thereuare]

In my add-in, trying to uninstall a command button that gets added in the Workbook_Open procedure doesn't want to work. In researching this, i found the following but don't understand it, but perhaps somebody could explain what it means and if this could explain why deleting the command button isn't working in the add-in (i even tried adding a simple MsgBox to the BeforeClose procedure and it didn't work, so apparently the add-in never gets to the BeforeClose event). Here's the info i found and don't understand:

Unlike sheet events, embedded charts and the Application object don't have events enabled by default. Before you can use events with an embedded chart or with the Application object, you must create a new class module and declare an object of type Chart or Application with events. You use the Class Module command (Insert menu) in the Visual Basic Editor to create a new class module.

To enable the events of the Application object, you'd add the following declaration to the class module.

Public WithEvents App As Application


After the new object has been declared with events, it appears in the Object box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure box.)

Before the procedures will run, however, you must connect the declared object in the class module to the Application object. You can do this from any module by using the following declaration (where "EventClass" is the name of the class module you created to enable events).

Public X As New EventClass


After you've created the X object variable (an instance of the EventClass class), you can set the App object of the EventClass class equal to the Microsoft Excel Application object.

Sub InitializeApp()
Set X.App = Application
End Sub


After you run the InitializeApp procedure, the App object in the EventClass class module points to the Microsoft Excel Application object, and the event procedures in the class module will run whenever the events occur.



Any insight?

 

[dk]

Re: Is this why BeforeClose doesn't work in my Add-In? (long

You should be able to just use the Workbook_BeforeClose event to remove the command button. What you've read (which I think is from Chip Pearson's site innit?) is if you want to monitor all workbook related activity. My guess is that you're using Excel 2000 without the relevant service pack. There was a bug in some versions of 2000 where the BeforeClose event never fires if the workbook's IsAddin property is true. Try putting your button removal code in the Auto_Close procedure e.g.

Sub Auto_Close()

'Code to remove your button here

End Sub

Note that this should go in a standard module. Let us know how you get on mate

 

[thereuare]

Re: Is this why BeforeClose doesn't work in my Add-In? (long

DK,

That seems to do it... i tested it with a simple MsgBox but will try it with the actual code in the morning as it's so late here it's getting early.

Is there a patch to fix this? Gee... i'd estimate about 16 hours spent fixing this and it turns out it's a bug in the software. Guess i've learned my lesson to keep my software updated!! I imagine the Service Pack is available for d/l at microsoft's site? If there are 3 Service Packs listed, do i need to d/l all 3 or does SP3 include the fixes that are included in 1 and 2?

If i plan on distributing this add-in, is there a way to ensure it's handled properly on the 'masses' computer.

THANK YOU for saving my sanity!

 

[dk]

Re: Is this why BeforeClose doesn't work in my Add-In? (long

Is there a patch to fix this? Gee... i'd estimate about 16 hours spent fixing this and it turns out it's a bug in the software.

Yes, we've all been there. I've spent literally days over the last year trying to fix problems that turn out to be bugs in MS software. It's taught me 2 things - 1) Keep my software up to date 2) If something weird is going on check Google Groups first.

Guess i've learned my lesson to keep my software updated!! I imagine the Service Pack is available for d/l at microsoft's site? If there are 3 Service Packs listed, do i need to d/l all 3 or does SP3 include the fixes that are included in 1 and 2?

Yes, the Office 2000 Update SP3 is a cumulative patch so you only need to install that.

If i plan on distributing this add-in, is there a way to ensure it's handled properly on the 'masses' computer.

THANK YOU for saving my sanity!

The only way to ensure it will work is to use the Auto_Close method, or ensure that if your users have 2000 then they have up-to-date service packs. Personally, I'd go with the first option.

 

[thereuare]

Re: Is this why BeforeClose doesn't work in my Add-In? (long

Sorry, but one more question...

I have no problem using the Auto_Close method, but does that still work on Excel 2000 and above? From what i have been reading (before i knew of this 'problem') it appeared to me that Auto_Close was 'old' and was replaced by _BeforeSave... but it seems like you're saying that both still work (they're supplemental, rather than 'in place of' and writers of code can use either one).

Thank you.

 

[dk]

Re: Is this why BeforeClose doesn't work in my Add-In? (long

Auto_Close works in 2000, XP and the current version 2003 so you'll be right for now. BeforeSave is the recommended method but if it doesn't work on some versions then obviously that's not a good recommendation!