Why wont this macro stop?

[liquidskin]

Hello,

Im pulling my hair out over here. All this macro is supposed to do is execute a data download every ten seconds until there is a value in C11 and then send an email and close.

What seems to be happening is that it downloads, emails, closes... but then in ten seconds the sheet reappears and does it all again. Help pleaseee!

Please Login or Register  to view this content.

[shg]

Take a look at the example in Help for the OnTime method to see how to cancel a pending OnTime setting.

[liquidskin]

I did a bit of further research and am now trying a different method

Please Login or Register  to view this content.

Why does the timer never kick in? The cell just blazes 1 thru 3 and never ends. Im just testing it, and I know it'll endlessly loop but Im expecting a pause and Im just not getting it no matter what I do.

[shg]

Please Login or Register  to view this content.

In the first statement, you're rescheduling loop1 to run about a million times a second, and there's nothing in the loop that effects C11, so it runs forever.

[liquidskin]

Thanks for the help Shg.

I decided to simplify this as much as I could. This is what Ive got, and it works just great.. Except for one thing..

Please Login or Register  to view this content.

The problem is that when this sheet opens. (Ive got it to AutoOpen open1() at 4pm daily thru task scheduler), I have other sheets open at the time. So, if I change focus to a different sheet the macro will attempt to run on that sheet. I tried using

Please Login or Register  to view this content.

in test2(), but then the window switches to SWEEP.XLS out of what Im working in. Can I activate it without switching the focus?

Any help is greatly appreciated

[shg]

Sounds like your two other macros need to make sure they're operating on ThisWorkbook and not ActiveWorkbook.

[liquidskin]

Sounds like your two other macros need to make sure they're operating on ThisWorkbook and not ActiveWorkbook.

Please Login or Register  to view this content.

Still doing the same thing. Say I have XYZ.xls open and this sheet opens, if I go over to XYZ.xls it executes the first statement in test2()

[shg]

Please Login or Register  to view this content.

Look in THESE macros.

[liquidskin]

Please Login or Register  to view this content.

Look in THESE macros.

I realized I botched that up after I did it. Clear and SecurityDistribution are now being called from thisWorkbook, but its STILL doing it.

[shg]

Liquidskin, it doesn't matter WHERE they are being called from. Look in the code and ensure they operating on the workbook you want them to.

By default, any range reference is to the ACTIVE workbook. You need to change those references to refer to a PARTICULAR workbook, maybe THISWorkbook.

[liquidskin]

Liquidskin, it doesn't matter WHERE they are being called from. Look in the code and ensure they operating on the workbook you want them to.

By default, any range reference is to the ACTIVE workbook. You need to change those references to refer to a PARTICULAR workbook, maybe THISWorkbook.

Shg,

Clear and Sec.Dist are in thisWorkbook.

This is what clear looks like.

Please Login or Register  to view this content.

so if Im running

Please Login or Register  to view this content.

Why would it be doing that? I must be missing something...

Appreciate it

[shg]

In Sub Clear, this:

Please Login or Register  to view this content.

refers to A10 on the ACTIVE sheet, in whatever workbook that is.

If you want it to refer to a sheet in the workbook that the code is in, you have to change it to,

Please Login or Register  to view this content.

[liquidskin]

In Sub Clear, this:

Please Login or Register  to view this content.

refers to A10 on the ACTIVE sheet, in whatever workbook that is.

If you want it to refer to a sheet in the workbook that the code is in, you have to change it to,

Please Login or Register  to view this content.

That results in a 1004 Runtime Error when I'm active in another workbook.

[shg]

There's no need to select:

Please Login or Register  to view this content.