Close App after certain period?

RedNeckGeek · #1 (**permalink**) 05-11-06, 10:13

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

Fazza · #2 (**permalink**) 05-11-06, 20:18

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

RedNeckGeek · #3 (**permalink**) 05-15-06, 07:01

Thanks for the reply, Fazza. I'll give this a go.

savbill · #4 (**permalink**) 05-15-06, 12:28

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.

Fazza · #5 (**permalink**) 05-15-06, 19:47

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

RedNeckGeek · #6 (**permalink**) 05-16-06, 08:29

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

Internet Services

Web Development

Digital Marketing

Consumer Tech