Close workbook after 60 minutes

simonw

New Member
Joined
Mar 5, 2004
Messages
32
Hi

I have a workbook that is used by many users, some users tend to leave it open for extended periods. I cannot make the workbook shared as it will loose functionallity.

I would like to insert a macro that will automatically save and close the workbook after 60 minutes.

Is this possible and can you help
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
hi -
try this codes;

paste this code in a standard module;
Code:
Dim timelimit, go, endtime, timeclose
Sub limitme()
timelimit = 3600
    go = Timer
    Do While Timer < go + timelimit
        DoEvents
    Loop
    endtime = Timer
    timeclose = endtime - go
    ThisWorkbook.Close True
End Sub

place this code in thisworkbook;
Code:
Private Sub Workbook_Open()
limitme
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
timelimit = 0    ' Set duration.
    limitme
End Sub

when the workbook is opened and the user did not change anything in the workbook, then the workbook will be saved and closed. when the user change something in the workbook, the timelimit will reset to zero. is this what you want?
 
Upvote 0
Hi,

Here is a reusable Sub that takes 3 arguments : 1- the name of the workbook to autoclose, 2- the time out in minutes, 3- a boolean to hold whether to save the workbook before closing it or not.

as opposed to the solutions in the links posted by firefytr, this is a more robust solution as it also detects mouse moves , forms handling...

place this in a standard module :

Code:
Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Declare Function GetLastInputInfo Lib "user32" (plii As Any) As Long
   
Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetForegroundWindow Lib "user32" () As Long

Private Type LASTINPUTINFO
   cbSize As Long
   dwTime As Long
End Type

Dim lngTimerID As Long
Dim strWorkbookName As String
Dim dblIdleTimeOut As Double
Dim blnSave As Boolean
Dim dblTimer As Double


Sub Workbook_AutoClose(Workbook_Name As String, idle_TimeOut As Double, Save_Wkb As Boolean)
  
    Dim OneMinute_Interval As Long
    
    strWorkbookName = Workbook_Name
    dblIdleTimeOut = idle_TimeOut
    blnSave = Save_Wkb
    OneMinute_Interval = 60000
    dblTimer = 1
    lngTimerID = SetTimer(0, 0, OneMinute_Interval, AddressOf TimerProc)

End Sub


'********* CallBack and supporting procedures **********
Sub TimerProc()

'    Static dblTimer As Double
    Dim lii As LASTINPUTINFO
    
    If GetForegroundWindow <> FindWindow("XLMAIN", Application.Caption) Then
        dblTimer = dblTimer + 1
        If dblTimer >= dblIdleTimeOut Then
            GoTo Close_Workbook
        Else: dblTimer = 0: Exit Sub
        End If
    End If
    lii.cbSize = Len(lii)
    Call GetLastInputInfo(lii)
    If ((GetTickCount() - lii.dwTime) / 1000) >= ((dblIdleTimeOut) * 55) Then
        GoTo Close_Workbook
    Else: Exit Sub
    End If
Close_Workbook:
    KillTimer 0, lngTimerID
    Application.OnTime Now + TimeSerial(0, 0, 1), "CloseWBK"

End Sub

Sub CloseWBK()

    If blnSave Then
        Workbooks(strWorkbookName).Close True
    Else
        Workbooks(strWorkbookName).Close False
    End If

End Sub


Code:
' ********* example *********
 Sub test()

    ' this will save and close this workbook if it has been idle for more than 1 minute
    
    Workbook_AutoClose Workbook_Name:=ThisWorkbook.Name, idle_TimeOut:=1, Save_Wkb:=True

End Sub

here is an example workbook :

http://www.savefile.com/files/17374


Regards.

note: edited to correct a logical error in the initial code.
 
Upvote 0
Hi, I've got a similar problem with a shared work book and I've managed to get this to work in mine - thanks. The only problem I have is that I don't fully understand how the timer limit works, ie how do I increase the value to, say 10 minutes?

Martin
 
Upvote 0
Please read - very carefully - the post above that you are using, especially this line...

Here is a reusable Sub that takes 3 arguments : 1- the name of the workbook to autoclose, 2- the time out in minutes, 3- a boolean to hold whether to save the workbook before closing it or not.

Then read the very last sub routine posted...

Code:
' ********* example *********
 Sub test()

    ' this will save and close this workbook if it has been idle for more than 1 minute
   
    Workbook_AutoClose Workbook_Name:=ThisWorkbook.Name, idle_TimeOut:=1, Save_Wkb:=True

End Sub

Notice the idle_TimeOut property???

The rest I leave to you.. :-)
 
Upvote 0
Oops! Pretty obvious really :oops: Many thanks.

I've had a play and it appears that this looks at any activity on the PC, not just that associated with the workbook. Could you clarify what I would need to leave out so that it only works if the Workbook isn't used for a period of time?

Many thanks
 
Upvote 0

Forum statistics

Threads
1,223,579
Messages
6,173,173
Members
452,505
Latest member
Alice34

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top