How can we disable an Excel Application Event?

Leon Lai
Lounger
Posts: 45
Joined: 12 Sep 2021, 14:50

How can we disable an Excel Application Event?

Post by Leon Lai »

Hello,

I have an Excel add-in which makes use of Excel Application Events, i.e., events in the add-in which respond to changes in the current worksheet.

The add-in has a UserForm1 which contains some Command buttons. One such button is called CLOSE THE USER FORM.

When I click on the Close The User Form Button, the UserForm_Terminate() Event is triggered.
I notice that the Application events still continue working.

What code must I put in UserForm_Terminate() to disable all Application Events?

Thanks
Leon Lai

User avatar
rory
5StarLounger
Posts: 747
Joined: 24 Jan 2010, 15:56

Re: How can we disable an Excel Application Event?

Post by rory »

Application events are only monitored by a WithEvents variable that you declared, so either you'll need to set that to Nothing when you terminate the form, or have it declared in the form so that it is destroyed automatically when the form unloads.
Regards,
Rory

User avatar
HansV
Administrator
Posts: 72594
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How can we disable an Excel Application Event?

Post by HansV »

Do you define the application events in a class module, say myClass?
If so, I assume that you initialize it with code like this:

Code: Select all

    Public myInstance As myClass
 
Sub StartIt()
    Set myInstance = New MyClass
End Sub
You can disable the event handling by inserting a line

Code: Select all

    Set myInstance = Nothing
where you want to do so, for example in UserForm_Terminate.
Regards,
Hans

Leon Lai
Lounger
Posts: 45
Joined: 12 Sep 2021, 14:50

Re: How can we disable an Excel Application Event?

Post by Leon Lai »

Hello HansV

Thanks for your reply.

I tried your code and it works fine.

However, I found that the foll. code also works:
Application.EnableEvents = False

Best Regards
Leon Lai

Leon Lai
Lounger
Posts: 45
Joined: 12 Sep 2021, 14:50

Re: How can we disable an Excel Application Event?

Post by Leon Lai »

Hello rori

Thanks for your reply.

Your suggestion works.

However, I found (quite to my surprise) that the foll. also works:
Application.EnableEvents = False

Best Regards
Leon Lai

User avatar
HansV
Administrator
Posts: 72594
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How can we disable an Excel Application Event?

Post by HansV »

That will work, but it will not just disable your custom application events, but ALL event handling in Excel
Regards,
Hans

Leon Lai
Lounger
Posts: 45
Joined: 12 Sep 2021, 14:50

Re: How can we disable an Excel Application Event?

Post by Leon Lai »

Thanks for the clarification!