Perplexing problem

[terryvg]

I have an application that consists of a workbook, call it Event for want of another name, that contains a reference to two other workbooks. One of these is nothing but code, nothing in it is ever changed, and the other is a list of names. The application is designed to accommodate any number of concurrently open Event workbooks.

Sometimes, not all the time but more often than not, when the first Event workbook is opened, which in turn opens the code and names workbooks via its references, this Event workbook either appears not to trigger the Workbook_Open event at all [as determined by the failure of a Debug.Print line in the Workbook_Open event] or, if it does trigger this event, it fails in a subsequent line of code "current_sheet = ActiveSheet.name". It's the 'Activesheet.name' that fails, not the assignment as determined by invoking the 'debug' option on the error dialog and doing "?current_sheet = ActiveSheet.name" in the immediate window.

If this first Event workbook is closed leaving the code and names workbooks still open, and then re-opened or other Event worksheets are subsequently opened, they all execute flawlessly.

This is driving me crazy. Is there some obscure timing race? Is the code work book sufficiently large to load in a timely manner? It's about 1.2Mb but even this size seems to vary based on principles unclear to me. What?

Whatever it is, it's most annoying and I would appreciate any insight anyone might have as to just what is happening here.

 

[XLfriend777]

Hi terryvg - What I have done in a case like this is to insert a MsgBox or two in certain locations that lets me know what the code is performing. That way a message will pop up when code is run and you can track what is happening. Example below:

Hope this helps.

MsgBox ("Just opened new workbook")

 

[ChrisM]

Any chance you have a phantom excel workbook open? You won't see it unless you go to Task Manger and look for Excel.exe process. I have a couple macros in Access that open Excel files, and I know they always leave a phantom session open. I have a VBS script I keep as a desktop icon to close all Excel sessions.

Check your task manager next time before you launch the Event file, see if there is already an Excel window open.

 

[terryvg]

Hi terryvg - What I have done in a case like this is to insert a MsgBox or two in certain locations that lets me know what the code is performing. That way a message will pop up when code is run and you can track what is happening...

Unfortunately Excel never invokes the Workbook_open event, nor any other as far as I can tell. It merely displays the worksheet that was active when the workbook was last saved and sits there, unable to do anything. That being the case, no trap is ever sprung.

Any chance you have a phantom excel workbook open? You won't see it unless you go to Task Manger and look for Excel.exe process. I have a couple macros in Access that open Excel files, and I know they always leave a phantom session open...

No phantom Excel processes. Or any other unaccounted for process for that matter.

Still without a clue as to what's going on and very perplexed...