Prev: Add-in doesn't load properly
Next: treeview control problems
From: Eric on 12 May 2010 13:09
I would like to save and close excel file after not active for 15 mins, to
define not being active is shown below:
1) not page up / down action
2) not switching any worksheet by clicking it
....
When the file is automatically opened based on schedule, I used to read this
report, as I sit in front of PC, I will read it and close it manually, but as
I am not available, and there is no action for this workbook, then I prefer
to save and close after 15 mins. However, I might open two workbooks at the
same time, so not being active only apply to this workbook only, since I
might click and reading another workbook at the same time. I get no idea on
which workbook function can perform this task, does anyone have any
suggestions?
Thanks in advance for any suggestions
Eric
From: ryguy7272 on 12 May 2010 14:52
Chip Pearson helped me with something similar just yesterday. Here's what I
ended up doing . . .

In VBA, go to the Tools menu, choose References, and then Windows Script
Host Object Model.

(This code goes behind the sheet)
Public CloseDownTime As Variant

Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime
EarliestTime:=CloseDownTime, Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:00:10") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub

Public Sub CloseDownFile()
On Error Resume Next

Dim R As VbMsgBoxResult
With New IWshRuntimeLibrary.WshShell
R = .Popup("Click 'Yes' if you would like another 10 seconds... If
the 'Yes' button is not clicked Excel will save your work and close the file
in 10 seconds.", 2, , vbYesNo + vbDefaultButton2)
End With

If R = vbYes Then
' user clicked yes
Call ResetTimer
Else
' user clicked no
Application.StatusBar = "Inactive File Closed: " &
ThisWorkbook.Name
ThisWorkbook.Close SaveChanges:=True
End If
End Sub

(This code goes in ThisWorkbook Module)
Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
ResetTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
ResetTimer
End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Eric" wrote:

> I would like to save and close excel file after not active for 15 mins, to
> define not being active is shown below:
> 1) not page up / down action
> 2) not switching any worksheet by clicking it
> ...
> When the file is automatically opened based on schedule, I used to read this
> report, as I sit in front of PC, I will read it and close it manually, but as
> I am not available, and there is no action for this workbook, then I prefer
> to save and close after 15 mins. However, I might open two workbooks at the
> same time, so not being active only apply to this workbook only, since I
> might click and reading another workbook at the same time. I get no idea on
> which workbook function can perform this task, does anyone have any
> suggestions?
> Thanks in advance for any suggestions
> Eric
 | 
Pages: 1
Prev: Add-in doesn't load properly
Next: treeview control problems