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
How can we disable an Excel Application Event?
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: How can we disable an Excel Application Event?
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
Rory
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can we disable an Excel Application Event?
Do you define the application events in a class module, say myClass?
If so, I assume that you initialize it with code like this:
You can disable the event handling by inserting a line
where you want to do so, for example in UserForm_Terminate.
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
Code: Select all
Set myInstance = Nothing
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: How can we disable an Excel Application Event?
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
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
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: How can we disable an Excel Application Event?
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
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
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can we disable an Excel Application Event?
That will work, but it will not just disable your custom application events, but ALL event handling in Excel
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: How can we disable an Excel Application Event?
Thanks for the clarification!