PDA

View Full Version : automatic save a file and close



maksinx
02-18-2007, 04:47 PM
hello everyone,

i have a file called shipping tracker which in on a share drive of the company and on a day to day basic 4-5 people use that sheet and enters the datas however only one can add dates while others read only,

what i want to do is if someone leaves the file inactive for 5 mins the file should save itself and close automaticly,

i am appreciated your help.

thanks in advance

Bob Phillips
02-18-2007, 05:08 PM
In the ThisWorkbook code module, add



'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
nElapsed = TimeSerial(0, 5, 0) '5 minutes

'start a timer to countdown inactivity
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub


'-----------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'-----------------------------------------------------------------
On Error Resume Next
Application.ontime EarliestTime:=nTime, Procedure:="Countdown", Schedule:=False

End Sub


'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime nTime, "Countdown", , False
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub


In a standard code module add



Option Explicit

Public nElapsed As Double
Public nTime As Double

'-----------------------------------------------------------------
Public Sub Countdown()
'-----------------------------------------------------------------
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

maksinx
02-19-2007, 01:01 PM
hello again,
Could you explain abit more?
Which part of the code i should write into a module and which part of the code i should write into thisworkbook

Will i write any codes in sheet1?

please advice

Bob Phillips
02-19-2007, 02:22 PM
I think I have already explained that.

dansam
02-20-2007, 09:21 AM
Hi maksinx,
I have attached a file that has the right solution which you want. Hope that it will help you?

dansam
02-20-2007, 09:21 AM
Hi,
You can change the time as you want...
thanks,
dan

maksinx
02-20-2007, 09:34 AM
dear dansam,
some how i cant see the attachment due to forum rules,
is there any other option so that i can see the codes,

please advice

thanks

maksinx
02-20-2007, 12:07 PM
hi dansam,
thank you for the attachment which works fine.Is there any chance to set up an idle mode for 10 mins?

Please advice

thanks again

dansam
02-21-2007, 09:52 AM
Hi
If you want to change the time delay then:
(1) Go to VBE (editor)
(2) From left side from VBAProject (time.xls) double click on Module 1
(3) Change the value of NUM_MINUTES to the number of minutes you want to leave the workbook unattended before closing.

Regards,
Dan

Guigy
05-01-2007, 02:00 AM
Hi Folks,

Been fishing around for this bit of code, googling it brought me to this magnificent forum. Bookmarked for much reading later! :)

Just wondering how I could display a countdown clock on screen to let the user know how much time they have.

Thanks in advance

Bob Phillips
05-01-2007, 02:15 AM
See http://www.xldynamic.com/source/xld.XtraTime.html

lucas
05-01-2007, 10:08 AM
Sub a()
Dim ReferenceDate As Single, Diff As Single
Dim Msg As String

ReferenceDate = CSng(CDate("5/13/2007 18:15"))
Diff = ReferenceDate - Now

Msg = Int(Diff) & " days and " & Round((Diff - Int(Diff)) * 24) & " hours till take-off."
ThisWorkbook.Sheets(1).Range("A9").Value = Msg
End Sub

dragon
05-13-2007, 03:40 PM
Hi Guys

Don't want to sabatage maksinx's thread but I am trying to achieve something along the same lines and wondered if there was a simple way to incorporate a 60 second countdown timer which would be displayed on a userform warning that theidle worksheet will be immenently saved and closed if there is no further activity. I have tried to amend xld's code above (which I found most interesting) to acheive this without success...but then again I am just an egg and still learning. Please let me know if I should be starting another thread in relation to this - just thought it might be good to keep this all together.

Thanks

Sorry, I think someone else has already asked that. I need spectacles....

Edited by dragon: I just checked out xld's link in post 11, but thats too complex. What I am thinking of would simply be a countdown displayed on a form. counting down from 60 to 0 with a 1 second delay between each change in number - it would be some kind of loop, but I can't figure it out.

Bob Phillips
05-14-2007, 02:04 AM
Create a form with a button named cmdOK and a textbox named txtCountdown and this code



Private Sub cmdOK_Click()
nSecsLeft = 60
ShowCountdownClock
End Sub



In a standard code module, add




Dim nTime As Double
Public nSecsLeft As Long


Sub ShowCountdownClock()
nSecsLeft = nSecsLeft - 1
If nSecsLeft > 0 Then
UserForm1.txtCountdown.Text = nSecsLeft
Application.OnTime Now() + TimeSerial(0, 0, 1), "ShowCountdownClock"
Else
UserForm1.txtCountdown.Text = "ALl done"
End If
End Sub

dragon
05-15-2007, 11:02 AM
Thank you xld. I have just tested your code on a makeshift workbook and it seems to do what I need it to do. I am on annual leave this week so will have to wait until next week to see if I can incorporate it into my project at work.

dragon
05-22-2007, 01:46 AM
Hi Guys, I've had problems trying to incorporate xld's code into my project. I have created a seperate workbook with just the code from posts 2 and 14 (with very minor modification) which I would like to attach to show the problem but don't know how to attach it to my reply. Basically, after 60 seconds of inactivity, a userform with a countdown appears warning that the workbook will be saved and closed in x seconds if there is no further activity. The user can click a cancel button to abort the save/close process. However the problem is once the user cancels the save/close process and then enters something into a cell the following happens:
"Run-time error '1004' Method 'onTime of object'_Application' failed" If you then click on debug you get the following message "Can't execute code in break mode" and it highlights the following line "Application.onTime nTime, "Countdown", , False". It seems to me that in aborting the countdown the program is leaving some processes still running and thats creating the problem (?). (I may be completely wrong there!!). Sorry for the long post but any ideas would be appreciated.

dragon
05-22-2007, 02:18 AM
Right, just figured out how to attach file...

Bob Phillips
05-22-2007, 03:10 AM
I don't quite get the sequence.

They have an amount of time to enter stuff. If this runs out, should the countdown kick in then, and if that runs down, you shut down, otherwise they get another amount of time to enter?

Is that a fair summary?

dragon
05-22-2007, 03:38 AM
Yes - If there is no activity in the workbook for a set amount of time (in this case 1 minute) the userform with a 60 second countdown kicks in. If the user cancels the userform or closes it by clicking in top right corner, everything should reset. The workbook should then start monitoring for inactivity again for another minute in this case, and if that happens the userform with the countdown kicks in once again and so on.

Sorry just to clarify, they can have as long as they want to enter stuff. They just can't leave the workbook open and forget about it for more then a minute.

dragon
05-24-2007, 06:26 AM
Hi Guys

I think I have managed to solve my problem by trial and error. I was previously not stopping/resetting the correct timer. Anyway, I am attaching the finished version if others are interested (or if they have suggestions to improve it!)

raymondlee
06-19-2007, 08:47 PM
Hi dragon,

I am encountering the same error "Run-time error '1004' Method 'onTime of object'_Application' failed", how did you solved it?

dragon
06-20-2007, 06:12 AM
Hello raymondlee

This is really xld's work that I was messing around with - don't understand the topic that well and it's been sometime since I really thought about it...but did you see the code in Excel file attached to post 20? That's the final version.

Basically, in the CMD1_click sub and the Userform_Query Close event you need to stop the ShowCountdownClock timer before restarting the inactivity Countdown timer. If you don't stop the ShowCountdownClock timer then VBA treats it as if you've done a control-break out of the procedure (at least thats how it seems to me - but don't ask me why!)