another VBA Question

  • Thread starter Thread starter dickharlo
  • Start date Start date
D

dickharlo

I've tried a couple options with no luck. Trying to come up with code
to Save / Close a spreadsheet automatically after x amount of time,
when worksheet is inactive. Preferable amount of time is 30 mintues
of inactivity. This final time, I was given the following
code....which does not seem to be working. Any ideas on what might be
wrong here?

Option Explicit

Dim Lasttime As Double
Dim Thistime As Double

Sub Auto_Open()
Application.OnEntry = "zerotime"
Lasttime = Now
Thistime = Now
CounterTime
End Sub

Sub Auto_Close()
Application.StatusBar = False
Application.OnEntry = ""
Application.OnTime Now() + TimeSerial(0, 5, 0),
procedure:="countertime", schedule:=False

End Sub

Sub CounterTime()
Thistime = Now - Lasttime
Application.StatusBar = "Unused for " + Format(Thistime, "hh.mm.ss") +
". Closes in 00.05.00"
If Thistime > TimeSerial(0, 5, 0) Then

ThisWorkbook.Close True
End
End If
Application.OnTime Now() + TimeSerial(0, 0, 1), "countertime"
End Sub

Sub Zerotime()
Lasttime = Now()
End Sub
 
Option Explicit

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
nElapsed = TimeSerial(0, 30, 0) '30 minutes
'start a timer to countdown inactivity
Application.OnTime Now + nElapsed, "Closedown"
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'-----------------------------------------------------------------
'close the timer
Application.OnTime Now + nElapsed, "Closedown",,False
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime Now + nElapsed, "Closedown"
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Put this code in a standard code module

Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub Closedown()
'-----------------------------------------------------------------
ThisWorkbook.Close SaveChanges:=True
End Sub
 
Back
Top