Timer in statusbar since last sheet change

**nickmax1** · 10-21-2014, 10:32 AM

I have code that closes my workbook after 1 hour of inactivity (ie no sheet change or event changes)

What i would love is to have the remaining time left for the user to make such a change (before it closes on them) visible in the statusbar. Any help there?

Code for ThisWorkbook

Please Login or Register  to view this content.

Code for module

Please Login or Register  to view this content.

**judgeh59** · 10-21-2014, 11:01 AM

these will turn on and allow you to display in the status bar and then return the status bar to regular XL...I'm not sure where you want to put this, but hopefully it will get you started...

Please Login or Register  to view this content.

**nickmax1** · 10-21-2014, 11:05 AM

judgeh59

Thanks for that, I know how to put something in the statusbar, I was hoping someone could figure out how to display the timer remaining as per the code above....as I have set the timer for 1 hour, so Excel is counting down from 1 hour , the result of which I want displayed and when the user makes a change or a calculation that timer is reset (by way of the Settimer routine) so you can see exactly how much time is left before the workbook selfcloses.

**judgeh59** · 10-21-2014, 11:12 AM

sorry, I wasn't sure exactly which part you were looking for....let me play a bit....

**judgeh59** · 10-21-2014, 11:59 AM

one of the hard parts is waiting the minutes to count off

.....it think this may get you what you need....

it actually runs a loop until the hour is up...the DoEvents allows you to "break" out of the loop by doing something like clicking in a cell. It doesn't seem to resource intensive. I didn't test every facet of the process because I'm not sure exactly what you are doing...the only way to manually break out of the loop to do a Ctrl-Break....hope this helps...

**nickmax1** · 10-21-2014, 12:06 PM

i get a run tim error 1004 Methos On time of object _Application failed error on opening, referring to

Please Login or Register  to view this content.

of the settimer?

**judgeh59** · 10-21-2014, 12:14 PM

I'm not really sure on that....I don't get an error and I didn't touch anything that Sub()....weird.....let me try a few things....

**judgeh59** · 10-21-2014, 12:16 PM

testing upload.....I file I uploaded on my desktop works fine....BUT, when I open the file I upload from the website, I get the same error. I'm going to try and upload again....If it doesn't work. I'm just post the code....

**judgeh59** · 10-21-2014, 12:17 PM

No Joy...

here is the code

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**coolblue** · 10-21-2014, 12:21 PM

I can't see the solution suggested by @judgeh59 coz I'm not at a pc right now, but it sounds like your doing a loop?
If so, I can suggest an alternate aproach:
Set up a second timer with the Procedure parameter set to a call back routine that updates the status bar and restarts the second timer - set it to 1 min or 30 sec perhaps. You then need some clean up code to stop the second timer in your shut down routine.

Sent from my iPad using Tapatalk

**nickmax1** · 10-22-2014, 04:45 AM

coolblue - how would you go about that?

Judgeh59 - sorry i just cant get your code to work without an error!

**coolblue** · 10-22-2014, 08:06 AM

Originally Posted by nickmax1

coolblue - how would you go about that?

Judgeh59 - sorry i just cant get your code to work without an error!

Like this for example...

In ThisWorkbook

Please Login or Register  to view this content.

In a module

Please Login or Register  to view this content.

I made the update 2 sec just so you don't get bored, but I would put it as 60 sec in practice and get rid of the batton thingy...

**nickmax1** · 10-23-2014, 05:45 AM

not sure i understand your code - how is it reset when a user is active in the sheet? It keeps ticking down no matter what interaction is used.

**coolblue** · 10-23-2014, 05:48 AM

Originally Posted by nickmax1

not sure i understand your code - how is it reset when a user is active in the sheet? It keeps ticking down no matter what interaction is used.

Correct. I'm just establishing the principle for the second timer.
You need to add the stuff for user activity resetting the timer back in yourself.

**coolblue** · 10-24-2014, 01:45 AM

Here is a complete solution in the style I would recomend.

Architecture
shutDownTimer.jpg
(maybe this link will work later... anyway, I added the pic to the attached spreadsheet)

ThisWorkbook Module

Please Login or Register  to view this content.

cShutDownTimer Class Module

Please Login or Register  to view this content.

cOnTime Class Module

Please Login or Register  to view this content.

cWorkbook Class Module

Please Login or Register  to view this content.

modCallBacks Standard Module

Please Login or Register  to view this content.

example spreadsheet attached Status Bar 20141023 r1.xlsm

**nickmax1** · 10-24-2014, 04:27 AM

wow - that is intricate!

I get an error on opening however - error 1004, method Ontime of application failed.
referring to:

Set shutDownTimer = New cShutDownTimer

**coolblue** · 10-24-2014, 04:31 AM

In the spreadsheet I uploaded?

Sent from my iPhone using Tapatalk

**nickmax1** · 10-24-2014, 04:34 AM

yes your spreadsheet delivers an error (i am using excel 2010)

**coolblue** · 10-24-2014, 04:46 AM

Don't open it from the Temp directory when you download it. Save the file to your desktop or one of your own folders and open the spreadsheet from there.
I don't know what browser your using but select Download File not Open file in your browser.

Sent from my iPhone using Tapatalk

**nickmax1** · 10-24-2014, 04:56 AM

aha that solved it (weird i never thought that should be an issue)

love you code its awesome, WAY beyond my understanding (where in the code does it reset the timer when the user makes an event change?)

rep added and solved.

**coolblue** · 10-24-2014, 05:02 AM

The path for the file in the browser cache appears to be some kind of abstraction... The folder referenced in the error message does not physically exist. That's why the callback can't be found by Application.OnTime. I never encountered this before and I don't really understand it but if you put yourself in the position if the macro and go looking for the file it says it can't find, then you won't be able to find it either. So it doesn't take long to work out what the problem is.

Sent from my iPhone using Tapatalk

**coolblue** · 10-24-2014, 05:14 AM

Originally Posted by nickmax1

aha that solved it (weird i never thought that should be an issue)

love you code its awesome, WAY beyond my understanding (where in the code does it reset the timer when the user makes an event change?)

rep added and solved.

Thanks for the rep.

The cWorkbook object is responsible for monitoring activity. It has a custom event called Activity that it raises whenever the various activity-related events in the workbook fire. You can easily customise which events in the cWorkbook object. When a new cWorkbook instance is created, it automatically subclasses whatever is currently "ThisWorkbook". That means any events raised by the ThisWorkbook object will first pass through the cWorkbook object. The subclassing is set up by this code in cWorkbook

Please Login or Register  to view this content.

This routine is automatically executed whenever a new instance is created.

The cShutdownTimer object invokes an instance of cWorkbook here:

Please Login or Register  to view this content.

and subsribes to the Activity event here:

Please Login or Register  to view this content.

...and thats where the shutdown timer is reset.

**nickmax1** · 10-24-2014, 05:26 AM

wow impressive.

quick question - the timer is currently set to 1 hour, but changing that to anything less (ie 0.5) kills it instantly. How do I set it for 1 minute, 10 minuites,30 seconds, or 10 seconds etc(for testing purposes)?

**coolblue** · 10-24-2014, 05:33 AM

Originally Posted by nickmax1

wow impressive.

quick question - the timer is currently set to 1 hour, but changing that to anything less (ie 0.5) kills it instantly. How do I set it for 30 seconds, or 10 seconds (for testing purposes)?

Find this code in the cShutDown object...

Please Login or Register  to view this content.

and enter the new value (e.g. 10 for seconds) in the ThisWorkbook Module here...

Please Login or Register  to view this content.

Also, add this line to the top of the ThisWorkbook Module if you just want to see a dialog on time-out...

Please Login or Register  to view this content.

**nickmax1** · 10-24-2014, 05:41 AM

ah great that seems to work.

now i can test to see if it closes, but it doesnt seem to - it gives me the boom! msgbox, but doesnt actually close the workbook?

**coolblue** · 10-24-2014, 05:44 AM

That's how to hack it. To fix it properly, do this...

In ThisWorkbook Module
Change this...

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

In the cShutDownTimer Class Module make these changes...

Add the CLng in the Class_Initialise sub here...

Please Login or Register  to view this content.

And change this...

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

And Bob's your ankle.

**coolblue** · 10-24-2014, 05:46 AM

Originally Posted by nickmax1

ah great that seems to work.

now i can test to see if it closes, but it doesnt seem to - it gives me the boom! msgbox, but doesnt actually close the workbook?

Correct, you can customise that behaviour in the ThisWorkbook Module here...

Please Login or Register  to view this content.

In fact the whole system is customised in the ThisWorkbook Module here...

Please Login or Register  to view this content.

**nickmax1** · 10-24-2014, 06:03 AM

coolblue

I uncommented the below code hoping it would close the workbook - but to no avail - it just resets the timer again

Please Login or Register  to view this content.

**coolblue** · 10-24-2014, 06:05 AM

Originally Posted by nickmax1

coolblue

I uncommented the below code hoping it would close the workbook - but to no avail - it just resets the timer again

Please Login or Register  to view this content.

Please Login or Register  to view this content.

will fix that.

**nickmax1** · 10-24-2014, 06:12 AM

thanks...interestingly it does close the workbook (though doesnt show the msgbox, though thats not a big deal) but after closing reopens it?

**coolblue** · 10-24-2014, 06:28 AM

Originally Posted by nickmax1

thanks...interestingly it does close the workbook (though doesnt show the msgbox, though thats not a big deal) but after closing reopens it?

Make this edit in cShutDownTimer...

Please Login or Register  to view this content.

**nickmax1** · 10-24-2014, 06:37 AM

aha that closes it fine thank you.