Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Close App after certain period?

    One area of our company has a spreadsheet that is shared by multiple locations. They have a problem with people opening the sheet, leaving it open, and then leaving their computer. They can never find who has it open, and therefore nobody else can get in. What they want is for the
    sheet to automatically save & close if it has been open and idle for X number
    of minutes.

    Since I'm the only person around who can write VBA, they asked me if there
    was anything I could do. I do most of my work in Access, and I could easily do it from there, using an OnTimer event. I'm not real sure about
    "event" based programming in excel. I can tell when a sheet was opened,
    and how long it's been open, but how do I reset my timer if the person that
    has it open does some form of update?

    Thanks in advance
    Inspiration Through Fermentation

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    Interesting question. This seems to work and could be useful to me too in something I developed.

    For the test, I used a worksheet SelectionChange event (the cell pointer is moved) to set an OnTime event to run. OnTime event must be in a normal module, not a worksheet or workbook module. You might want a different event depending on your spreadsheet. Maybe a SheetChange event for when a cell in any open workbook is altered?

    Anyway, two bits of code, this one in a standard module
    Code:
    Option Explicit
    
    Public mtLastChange As Date
    Public mtTimeLimit As Date
    
    Sub CloseFileIfInactive()
    
        If Now > mtLastChange + mtTimeLimit - TimeValue("00:00:01") Then ThisWorkbook.Close
    
    End Sub
    And in my test, this code in a worksheet module,
    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
        mtTimeLimit = TimeValue("00:05:00")
    
        mtLastChange = Now
        
        Application.OnTime mtLastChange + mtTimeLimit, "CloseFileIfInactive"
    
    End Sub
    Modify to suit. Especially the Workbook.Close you might do differently - save file & then close, whatever. When testing, I set the mtTimeLimit to "00:00:10" but put it to 5 minutes above to suit real application.

    HTH,
    Fazza

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks for the reply, Fazza. I'll give this a go.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    You could set up the workbook as a Shared Workbook. Then multiple people could have it open and edit at the same time. See Excel Menu 'Tools->Share Workbook...'

    Another idea is to have the workbook open Read-only by default. If you have multi-users needing to edit the workbook you may still need to use a timer to check if they have it open in edit mode for an extended period.
    ~

    Bill

  5. #5
    Join Date
    Feb 2006
    Posts
    113
    Hi RedNeck,

    Thinking about this again, be aware that the sample I gave sets the "CloseFileIfInactive" to run (so many minutes after) EVERY worksheet SelectionChange event. (And although running many times, closing only if the file has been inactive for a while.)

    This could be impractical on a major/busy spreadsheet.

    See how you go.

    Fazza

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Fazza,

    That works well. I made a few changes, in case someone opens the
    workbook, and walks away without making changes. It also accounts for
    changes made on any worksheet in the workbook.


    Code:
    Private Sub Workbook_Open()
      dtmTimeLimit = TimeValue("00:01:00")
      dtmLastChange = Now
      Application.OnTime dtmLastChange + dtmTimeLimit, "CloseFile"
    End Sub
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      dtmLastChange = Now
      Application.OnTime dtmLastChange + dtmTimeLimit, "CloseFile"
    End Sub
    and in the standard module...

    Code:
    Public dtmLastChange As Date
    Public dtmTimeLimit As Date
    
    Sub CloseFile()
      If Now > dtmLastChange + dtmTimeLimit - TimeValue("00:00:01") Then
        ThisWorkbook.Close True
      End If
    End Sub
    Thanks again for the help.

    Bill,
    I didn't know about the shared workbook option. That may work in this case also. Thanks
    Inspiration Through Fermentation

Posting Permissions

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