close and save workbook on timer

[jtodd]

Hi
Sure this is a simple one .
I have a workbook that is used by multiple users ,
It updates every 10 mins with data from another source .
User have a habit of opening it and leaving it open ,
How can I close and save the workbook after a specified time , say half an hour

 

[Scott T]

This may help you

https://excelribbon.tips.net/T008192_Forcing_a_Workbook_to_Close_after_Inactivity.html

 

[AlphaFrog]

Put this in the ThisWorkbook code module.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeClose(Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    [color=darkblue]Call[/color] Closer_Cancel
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
    [color=darkblue]Call[/color] Closer_Start
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Put this in any standard code module e.g.; Module1

[color=darkblue]Option[/color] [color=darkblue]Private[/color] [color=darkblue]Module[/color]


[color=darkblue]Dim[/color] CloseTime [color=darkblue]As[/color] [color=darkblue]Date[/color]


[color=darkblue]Public[/color] [color=darkblue]Sub[/color] Closer_Start()
    CloseTime = Now + TimeValue("00:30:00")
    Application.OnTime CloseTime, "Closer"
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Public[/color] [color=darkblue]Sub[/color] Closer_Cancel()
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.OnTime CloseTime, "Closer", Schedule:=[color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Closer()
    ThisWorkbook.Close SaveChanges:=[color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]