Re: Automatic saving and closing

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks Bernie, works a treat.
I have noticed however, if the user enters data but doesn't tab out or press
<enter> to move to another cell, the workbook doesn't close.
--
tia

Jock


"Bernie Deitrick" wrote:

Jock,

You got that message because there was no ontime event to cancel. I assumed that you would first
save the workbook and your users would re-open it prior to making any changes.

Either run this macro to initialize the ontime method:

Sub StartOnTime()
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

or add

On Error Resume Next

to the sheet change event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next
Application.OnTime RunTime, "SaveAndCloseMe", , False
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub


Sorry about that,
Bernie
MS Excel MVP


"Jock" <Jock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:89C696BF-CC56-4853-AB72-9E0693168C8B@xxxxxxxxxxxxxxxx
If only it was that easy.
All I did (after entering the code) was change the value in a cell and
tabbed out of it. The error message appears immediately.
--
tia

Jock


"Susan" wrote:

maybe because you're forcing it to run & 20 minutes hasn't elapsed yet?
or maybe not - just a thought.
:)
susan


"Jock" <Jock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:072A1815-3857-40B0-9A5E-4919CBB1AC22@xxxxxxxxxxxxxxxx
Bernie,
Copied & pasted your code.
However, I get an error message:
Run-time error '1004'
Method 'on time' of object '_Application' failed

What have I done wrong here?

--
tia

Jock


"Bernie Deitrick" wrote:

Jock,

Put this code into a regular codemodule:

Public RunTime As Date

Sub SaveAndCloseMe()
Application.DisplayAlerts = False
ThisWorkbook.Close True
Application.DisplayAlerts = True
End Sub


And put this code into the ThisWorkbook object's codemodule:

Private Sub Workbook_Open()
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.OnTime RunTime, "SaveAndCloseMe", , False
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

It will save and close the workbook after it hasn't been changed for 20
minutes.

HTH,
Bernie
MS Excel MVP


"Jock" <Jock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8D50B92-D174-4680-85AC-694E04205CAA@xxxxxxxxxxxxxxxx
Quite often, somone in our office has a certain file open to which
others
need access. The person who has it open hasn't made changes for, say 20
mins.
Is it possible to have the spreadsheet automatically save and close
after a
specified time period has elapsed?
--
tia

Jock









.

Relevant Pages

  • Running Macro on every sheet in Workbook !!!
    ... in Workbook "ClearOrClosedSent.xls". ... The Worksheets will have different names however. ... Can anyone please tell me how to apply the Sub on all worksheets in the ... TIA ...
    (microsoft.public.excel.programming)
  • Re: Application.quit
    ... You code would close the workbook with the code which causes the code to ... Code is assigned to button into userform ... > End Sub ... >> TIA ...
    (microsoft.public.excel.programming)
  • Adding code to worksheet
    ... Curious on how to add code to a specific sheet within a workbook. ... "end sub" ... But is there a way I can place code into a sheet rather than to a module? ... TIA ...
    (microsoft.public.excel.programming)
  • Re: script to send data from worksheet to multiple workbooks
    ... info to a master workbook. ... Public wsMyIndirectSheet As Worksheet ... Public Month, m, PrevMonth As Integer ... Public Sub MonthNumber() ...
    (microsoft.public.excel.programming)
  • RE: Using excel to Determine if a File Exists in SharePoint
    ... The references I have checked in this particular workbook are listed below; ... 'Get file path from file name ... Sub CheckIfexists() ... You just need to know your local sharepoint ...
    (microsoft.public.excel.programming)