application.ontime running time viewer

Hi,

I have scheduled macro's running ontime

Please Login or Register  to view this content.

is there a wait to let in a cell on the sheet see (the macro is still running) by showing the time running starting from 15 min -1 sec -2 sec -3 sec till 00.00.00 so when the macro rerun its back on 15 min and counting down ?

thanks
chris

Hi Chris,

See the attached file that contains the following code which may point you in the correct direction.

In the ThisWorkbook Code module - to make sure the Timer is stopped when the Workbook Closes:

Please Login or Register  to view this content.

In an ordinary code module such as Module1:

Please Login or Register  to view this content.

Lewis

And another example, with the 'UserForm' "window":

1. Code in 'Sheet1' module (the trigger point is in cell 'I2'):

Please Login or Register  to view this content.

2. Code in module 'Module1':

Please Login or Register  to view this content.

3. Code in 'UserForm1' module:

Please Login or Register  to view this content.

Hi,

Thanks to you both for writting the code, wow it looked a lot more compicated then I thought. I expected to just see a code of 4 lines.. that put in a cell 00:15:00 and counting down to 00:00:00 and then when macro run again reset it to 00:15:00.
But after more study, it's seems like not simpel to just see the running time in a cell in excel.

I tried first theone from Porucha (timebook3) cause looked more simple or to what I subscribed above. actually had some issues with cat window, cause seems like in the 15 min waiting periode I can not use excel. I also didn't succeed in changing the time .. sure it is my limit knowledge of code.

Then I tried the first one from Lewis (appl ontime) If I understand good, this one replace my code line Application.OnTime Now + TimeValue("00:15:00"), "HOURKuC" and act like an outside controlable app to run.
Looks great, but still some questions hang in my head.

so i need to remove my ontme line ?
it seems like when the timer is running I can work on in excel thats good
if the macro go into debug Will I see that the timer stop ?
(extra) is it possible to see like running = green , stand still is red, or get an sound alert when it stop ?

Thanks for your marvelous app
Chris

Then I tried the first one from Lewis (appl ontime), this one replace my code line Application.OnTime Now + TimeValue("00:15:00"), "HOURKuC"

Yes. See the code BELOW copied from above. To make my code work with your code:
a. See the line in red below
b. Comment out and/or change the cells of any 'Range("A9") =' or 'Range("A10") =' lines as required. I recommend changing things one line at a time, to see if you like the change. Make Backup copies of your file as you make changes to prevent disaster.

Please Login or Register  to view this content.

---------------------------

if the macro go into debug Will I see that the timer stop ?

Yes. Nothing will happen when in DEBUG mode. Excel macros often appear to do more than one thing at a time, but actually can not. When in DEBUG mode, all Macro activity stops.

---------------------------

(extra) is it possible to see like running = green , stand still is red, or get an sound alert when it stop ?

It is not possible to the best of my knowledge to change colors using VBA while in DEBUG mode (except by manually entering a command in the Immediate Window [CTRL G]).
While the Macro is running it is possible to change colors, such as yellow when less than one minute remaining, and orange when less than 10 seconds remaining until your macro runs.

---------------------

The following may help you debug your code now and in the future:
Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm

Lewis

Hi Lewis,

Thank you for your update, your help and your respect for someone not as skilled like you. Clear explanation :-)
I could implement it to my macro, I could modify I bit to my wishes..

finaly 1 issue left

Please Login or Register  to view this content.

"

If I go to another sheet in the workbook or outside the workbook it print that line in cell A10 of another sheet.. everywhere
I tried to solve it by let him the right sheet first. but then I am not able to go to another sheet cause every sec he return to the timer sheet..
any idea ? and it is ness to see the time running (or I have to run it in a seperate Excel (ALT+Excel)

Greets
Chris

If I go to another sheet in the workbook or outside the workbook it print that line in cell A10 of another sheet.. everywhere
I tried to solve it by let him the right sheet first. but then I am not able to go to another sheet cause every sec he return to the timer sheet..
any idea ?

Please Login or Register  to view this content.

Lewis Lewis Lewis.

YOU SOLVED IT ALL. ! My system is working great now like I want thanks to you !

big Thanks
Christian