Workbook_SheetChange event loses reference to Workbook

[Tim_Excel_]

Hi forum,

I have a Workbook_SheetChange event for another workbook (other than the workbook that holds the code). At the end of a lengthy Sub, I call for the Sub that defines the Workbook's name that the event needs to work for:

Sub step1()

'''''code''''

NameGrabber.DefineWorkbookName

End Sub

Dim oWb As New Updater

Sub DefineWorkbookName()
Application.EnableEvents = True
Dim AddNew As Workbook
    Set AddNew = Workbooks("test1")
    Set oWb.Workbook = AddNew


End Sub

Then, in a Class Module sits the Workbook_Sheetchange event:

Public WithEvents m_wb As Workbook

Public Property Set Workbook(wb As Workbook)
    Set m_wb = wb
End Property


Public Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property

Public Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range)

''''code''''

End Sub

Problem is, the SheetChange event will only run if I manually run the DefineWorkbookName Sub myself first, even though the code is set to run (and does run) through this sub already.

Is there any way I could have the SheetChange event run without having to run the DefineWorkbookName sub manually first? Thanks a lot!

 

[RoryA]

even though the code is set to run (and does run) through this sub already

Not sure what you mean by that? If you don't run the code to set up the listener, nothing will be listening for that event.

 

[Tim_Excel_]

Rory,

Since I call the DefineWorkbookName Sub, the Workbook name is defined for the WorksheetChange event. What I am trying to say is that this sub already runs in my code, but I STILL have to run it a second time manually because otherwise the WorksheetChange simply doesn't run.

 

[RoryA]

Then I suspect something in your code is causing a state loss and resetting your oWb variable.

 

[Tim_Excel_]

What would you suspect is causing this loss? I don't see the cause, the DefineWorkbookName Sub runs on the very end and then only the WorksheetChange event is supposed to run. There is no code that runs between setting the oWb variable and running the WorksheetChange event.

 

[RoryA]

In the absence of any code to go on at all, I'll take a wild stab at you adding activex controls to a worksheet.

 

[Tim_Excel_]

Rory,

There is an ActiveX button on the workbook that holds the code. This button is used to start the sep1 Sub that ends in the DefineWorkbookName sub being called. Could I maybe write the workbook name in a cell somewhere and then refer to that cell in the WorksheetChange event?

 

[RoryA]

The button would only be an issue if you created it in the code. Writing the name won't help if you don't have a listener responding to the event. Without any code to look at I can't really assist you further.

 

[Tim_Excel_]

Rory, perhaps you could educate me on what a listener is? I am very much a novice when it comes to this kind of code, I have pulled the code for the WorksheetChange event from another forum.

I could share with you the code but I assure you it would be to no assistance to solving this problem. None of the code not shown in my primary post has anything to do with the WorksheetChange event, and the code in the event itself does not even run like I mentioned.

 

[RoryA]

Your Updater class is the listener - it has declared a WithEvents variable, so it sits there "listening" for that event being raised, then does its thing.

I could share with you the code but I assure you it would be to no assistance to solving this problem.

How do you know that? You say that the code does work if you run that routine yourself, but doesn't when it's run from your code. That would tend to imply that the problem is with the code you are not posting, would it not?