inactive worksheet

[Looping through]

Is there a way to have a pop up notice appear if a open workbook is open but
no information is being inputed or no cells are being activated or tabs being
selected?

Basically tell the current user to close the workbook due to inactivity.

I have a shared file that sometimes get's left open on someone computer and
that person moves on to other tasks without closing it.

Is this possible?
TIA
Peter

 

[Mike H]

Hi,

here's a way
http://www.ozgrid.com/forum/showthread.php?t=42169

Mike

 

[Tom Hutchins]

One way...

In the ThisWorkbook module of the workbook, paste the following code:

Private Sub Workbook_Open()
'Set StartTime when the workbook is opened.
StartTime = Timer
'Schedule a call to CheckTime in the future to check elapsed idle time.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'Something changed in the workbook, so reset StartTime.
StartTime = Timer
End Sub

In A VBA code module in the same workbook, paste this code:

Global StartTime As Single
Global Const TimeLimitInMinutes = 180 'idle time threshold
Global Const TimeCheckDelay = "00:10:00"

Sub CheckTime()
Dim NewTime As Single
'Get the time (seconds past midnight) now.
NewTime = Timer
'If StartTime was yesterday, add 86400 seconds to NewTime.
If NewTime < StartTime Then
NewTime = NewTime + 86400
End If
'If TimeLimitInMinutes has expired since StartTime was last
'updated, close the workbook without saving changes.
If (NewTime - StartTime) > (TimeLimitInMinutes * 60) Then
ThisWorkbook.Saved = True
ThisWorkbook.Close SaveChanges:=False
Else
'Otherwise, schedule a call to CheckTime in the future to check
'again later.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End If
End Sub

The code above is set to close the workbook without saving changes if it is
idle for more than 180 minutes (3 hours). It will check every 10 minutes.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

 

[John Wilson]

Peter,

What if there's no one there to see the pop-up?

Why not use an inactivity timer and close it down automatically.
You can find some code for one here:

http://tinyurl.com/5k2ydn

John

 

[Looping through]

You are awesome

One way...

In the ThisWorkbook module of the workbook, paste the following code:

Private Sub Workbook_Open()
'Set StartTime when the workbook is opened.
StartTime = Timer
'Schedule a call to CheckTime in the future to check elapsed idle time.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'Something changed in the workbook, so reset StartTime.
StartTime = Timer
End Sub

In A VBA code module in the same workbook, paste this code:

Global StartTime As Single
Global Const TimeLimitInMinutes = 180 'idle time threshold
Global Const TimeCheckDelay = "00:10:00"

Sub CheckTime()
Dim NewTime As Single
'Get the time (seconds past midnight) now.
NewTime = Timer
'If StartTime was yesterday, add 86400 seconds to NewTime.
If NewTime < StartTime Then
NewTime = NewTime + 86400
End If
'If TimeLimitInMinutes has expired since StartTime was last
'updated, close the workbook without saving changes.
If (NewTime - StartTime) > (TimeLimitInMinutes * 60) Then
ThisWorkbook.Saved = True
ThisWorkbook.Close SaveChanges:=False
Else
'Otherwise, schedule a call to CheckTime in the future to check
'again later.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End If
End Sub

The code above is set to close the workbook without saving changes if it is
idle for more than 180 minutes (3 hours). It will check every 10 minutes.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch