VB code error when workbook loses focus

Good morning: I think I have run out of ideas on how to solve a macro problem:

Overview: I have a workbook named MainPage.xls. This mainpage has several buttons and serves as a switchboard to launch other workbooks. Attached to this Main Page are two worksheets, "Source" and "Value1". The "Source" worksheet is graphical in nature and is used to collect data via a DDE channel to some production industrial processors and then on a timer "log" the data to cells in the "Value1" worksheet. Once the VB code is launched from the "Source" worksheet (via a start button), the code continues to run until it is stopped (via a stop button). The data is then graphed and printed out, etc.

This works very nicely and provides "live" graphing of some constantly changing data.
Once the "Source" worksheet data VB code is running, I need to be able to minimize the worksheets and return to my Main Page and further be able to open any one of the other workbooks on the Main Page and work with that page(s). Thus far, I can minimize the "Source" and "Value1" worksheets and return to the Main Page and the VB code runs along nicely in the background.

However... IF I attempt to open any other workbook from the Main Page, my data collection/graphing code stops and I get a Run-time error 9, subscript out of range... and Excel completely shuts down.

I know this must have something to do with the worksheet that runs the VB code losing focus when another workbook is opened.

Anyone that may have some insight would be much appreciated. I have tired my mind trying to figure out a way to keep my code running. I can post the code (have to figure out how to do that first) if needed.

Some parts of Excel I get along with pretty well. Other parts (this!) I am stumped. Thanks in advance for any help.

Hi & Welcome to ExcelForum!

It would definitely help if you could post the code as it might be something as simple as removing references to the Activesheet/activeworkbook (can't tell without seeing the code though). Also if you have any global variables declared in the project it would be useful seeing these too. Just copy and paste the code into your reply and surround the entire code with code tags - this means write [ code ] before the code and then [ /code ] after the code (without the spaces).

Good morning, Richard. Fast reply is much appreciated.

Ok. The following is the code that I use to open my worksheet "Source", which I remind you is actually a worksheet attached to my Main Page.xls.:

Please Login or Register  to view this content.

The next lines are the macro that "starts" my data collection:

Please Login or Register  to view this content.

And the next is the timer and code that logs the data to my "Value1" worksheet:

Please Login or Register  to view this content.

Richard, there are a couple of other macros that only serve to print my graph, and clear the data from "Value1" cells, etc.

I hope I have done this correctly and have laid this out in some kind of understandable form

Your highlighted code that errors refers to the Activeworkbook - if you have opened up another workbook and that is currently actiove when the called procedure runs, you have a problem (and get the subscript out of range error if the activeworkbook does not contain Source or Value1 worksheets). So you can try just prefixing your sheets() with the workbook name (actually you won't even need the workbook name, as you can use the ThisWorkbook object):

Please Login or Register  to view this content.

Richard

Hi Protius,
I added the closing tag to your code.
You can add begin and end tag in " advanced mod" in the thread reply form.
Just select your code and press the " code " icon
Voilą

Cheers and glad to have you on board

Thanks, Arthur and Richard for your responses. Arthur, I added the With ThisWorkbook and End With to my code and it still is giving error with same highlighted line.

If this matters, the code is located in Module 4 of my MainPage.xls tree. Since my "Source" worksheet from which I launch the macro is attached to MainPage.xls I think it should be okay?

Is there a more precise way to reference With ThisWorkbook in the line? Thank you for helping in this continuation.

Richard:

I tried the changes you suggested using "With ThisWorkbook" and "End With". Same results.

I am wondering if my code is just located in the wrong place in the MainPage.xls workbook? Following is my various pieces of code and their respective locations:

Please Login or Register  to view this content.

Above is located as an Object in the "Source" worksheet

Please Login or Register  to view this content.

Is located as an Object in the "Value1" worksheet

Please Login or Register  to view this content.

Is located in Module 4 of MainPage.xls

Do you see anything out of line with where code is placed?

Did you include the dots (.) in the code:

Please Login or Register  to view this content.

Wow, those are small dots

I guess my bifocals need tuning up. Let me try the code again and I will reply back here in a bit. Thanks, Richard for hanging in here with me.

Ok, Richard. We made some headway here. I can now move around to other workbooks and Excel does not give error and shutdown..however, the data collection stopped working. e.g., no values are written to my "Value1" worksheet from the cells in "Source".

??

Richard:

Ok. For whatever reason, when I entered the changes in the code (adding those bifocal dots ) I must have entered to many spaces?. At any rate... everything is working just fine now. Perfect solution here!!! I really appreciate your help, and Arthur's help. I need to read more about the fine details of VB.

It looks great. Tonight is my Friday. Tomorrow I can hand this over to the powers that be, and let them know it is DONE. Again... very very fine business in your help. Look for you here on JB's ExcelTip again.

Best regards.

I'm glad you resolved it! I haven't been able to log in here until today so i was wondering how you were getting on

Richard

Admin:

Please mark this thread as SOLVED. Thanks.

Admin:
Please mark this post as SOLVED. Thank you.