Event in Workbook not working when reopening

1
votes

I have an Excel macro that creates a new custom workbook with events. The macro also copies the .bas and .cls files onto the new workbook and changes the link to be the new workbook.

When the new workbook is created, everything, including events works fine. But when I close it and reopen it, the events do not work anymore. I realize the new workbook value is been lost. Since I have a button on the sheet I added the following to try to recreate it but no luck:

Dim Newbook As New eventWB
Dim thisWB As Workbook
Set Newbook.Workbook = ActiveWorkbook
Set thisWB = Newbook.Workbook

// extra code to gather information
1
vbaexcel
do you place the event code inside the sheet modules or ThisWorkbook module of the newly created workbook? – Scott Holtzman
Hello @ScottHoltzman I have the code inside the eventWB.cls module which is located inside the new workbook – paul590
I am not 100% familiar with classes, but how would for instance a Worksheet_Change event know to fire if I change a cell on Sheet1 in the new workbook if the code is in the class module? – Scott Holtzman
since its inside the class module, I am creating the workbook as new eventWB. So its not a regular workbook. Each time I make a change on the cell, the workbook has this function inside of it so it will look it up as long as the application.enableEvents = true – paul590
Just to understand, you've created a custom class that catches all workbook events (possibly even custom events you generate). In your current (active) workbook, you use Workbooks.Add to create a new workbook and then copy your code modules/classes over to it. On a sheet in the new workbook, you have the code listed above (which creates an object of your custom event class). If you close all workbooks and then open the macro-created workbook, events do not work. Is this correct? – PeterT

1 Answers

0
votes

I beleive I found the solution to my problem. I was able to make it work by adding an extra lin on my workbook class:

Public WithEvents m_events As Application

Every time I open the workbook I call I set it as:

Newbook.m_events = Application

And it does the trick the events work again. Hope this helps anybody with the same issue! Thank you again!