CODEWITHSUNDEEP
excelevent-handlingvba
paul590
paul590

Reputation: 1435

Excel Events not working

I have a macro that creates a workbook from another workbook with events. I have created a custom workbook class called eventWB to add the events. This works great, except when I close the new workbook created and re open it, the events dont work. I tried adding the following function to the ThisWorkbook file for the new workbook but it does not seem to enable the events. I added an alert to make sure the function is working and it works great. If you have any ideas please let me know.

Function in ThisWorkbook:

Sub Workbook_Open()
Dim Newbook As New eventWB
Set Newbook.Workbook = ActiveWorkbook
Set Newbook.m_events = Application
Set thisWB = Newbook.Workbook
Application.EnableEvents = True
End Sub

Upvotes: 0

Views: 1072

Answers (1)

paul590
paul590

Reputation: 1435

I solve this issue by adding a function on the module im using:

Function reopenResetVariables()
    Application.EnableEvents = False
    Dim thisWB As Workbook
    Set Newbook.Workbook = ActiveWorkbook
    Set Newbook.m_events = Application
    Set thisWB = Newbook.Workbook
    Application.EnableEvents = True
End Function

and adding call reopenResetVariables to the Workbook_Activity function when opening excel

Upvotes: 1

Related Questions