Perplexing problem

terryvg

New Member
Joined
Jan 20, 2014
Messages
2
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
649
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.

Code:
MsgBox ("Just opened new workbook")
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Jan 20, 2014
Messages
2
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,534
Messages
5,596,726
Members
414,093
Latest member
Tegglet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top