Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inactive worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inactive worksheet
Hi,
here's a way http://www.ozgrid.com/forum/showthread.php?t=42169 Mike "Looping through" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inactive worksheet
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 "Looping through" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inactive worksheet
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 he http://tinyurl.com/5k2ydn John "Looping through" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inactive worksheet
You are awesome
"Tom Hutchins" wrote: 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 "Looping through" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I email an inactive copy of a worksheet | Excel Discussion (Misc queries) | |||
Worksheet events inactive | Excel Programming | |||
With block does not work on inactive worksheet? | Excel Programming | |||
Assign Range - Inactive Worksheet | Excel Programming | |||
How to get the activecell of inactive worksheet ? | Excel Programming |