Results 1 to 3 of 3

Thread: autosave and close error

  1. #1

    autosave and close error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have mutliple team members using one spreadsheet and on the chance someone goes to lunch and leaves the spreadsheet open, no one else can't be accesssed.
    What I need is a macro that autosaves the shared workbook, then closes it if left idle for a period of time (say 5 mins).( For your information: I would like to autosave and close the shared workbook ). I am using the following code, but the problem is every 20 s my excel is closing and restarting again automatically. Does any one knows why it is restarting ?
    thanks
    Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveSheet.AutoFilterMode = False
    Call Disable

    End Sub
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call Disable
    Call SetTime
    End Sub
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
    As Excel.Range)
    Call Disable
    Call SetTime
    End Sub
    Private Sub Workbook_Open()
    UserForm1.Show
    Call Tempo
    MsgBox "This workbook will auto-close after 20 seconds of inactivity"
    Call SetTime
    End Sub

    module code:
    Dim DownTime As Date

    Sub SetTime()
    DownTime = Now + TimeValue("00:00:20")
    Application.OnTime DownTime, "ShutDown"
    End Sub

    Sub ShutDown()
    ThisWorkbook.Save
    ThisWorkbook.Close
    End Sub

    Sub Disable()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _
    Schedule:=False
    End Sub

    Any help would be appreciated

  2. #2
    Code:
    Sub ShutDown()    
        Application.OnTime DownTime, "Shutdown", , False
        ThisWorkbook.Save
        ThisWorkbook.Close
    End Sub

  3. #3
    thanks for you rreply.
    Its showing error as
    Run time error 1004
    Method 'ontime of object' Application failed
    and highlighting Application.OnTime DownTime, "Shutdown", , False
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •