Automatic save a file and close

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

In the ThisWorkbook code module, add

Code:

---

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

Code:

---


Option Explicit
 
Public nElapsed As Double
Public nTime As Double
 
Public Sub Countdown()
    ThisWorkbook.Save
    ThisWorkbook.Close
End Sub

---

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

I think I have already explained that.

Hi maksinx,
I have attached a file that has the right solution which you want. Hope that it will help you?

Hi,
You can change the time as you want...
thanks,
dan

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

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

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

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

See http://www.xldynamic.com/source/xld.XtraTime.html

Code:

---

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

---

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.

Create a form with a button named cmdOK and a textbox named txtCountdown and this code

Code:

---

Private Sub cmdOK_Click()
    nSecsLeft = 60
    ShowCountdownClock
End Sub

---

In a standard code module, add

Code:

---

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

---

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.

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.

Right, just figured out how to attach file...

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?

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.

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!)

Hi dragon,

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

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!)