And then, how can i check the edit cell mode by using excel.application object?

"Bernie Deitrick" wrote:

Jock,

VBA code is disabled when Excel is in Edit mode, so there is no recourse except user training.

HTH,
Bernie
MS Excel MVP


"Jock" wrote in message
...
Thanks Bernie, works a treat.
I have noticed however, if the user enters data but doesn't tab out or press
<enter to move to another cell, the workbook doesn't close.
--
tia

Jock


"Bernie Deitrick" wrote:

Jock,

You got that message because there was no ontime event to cancel. I assumed that you would first
save the workbook and your users would re-open it prior to making any changes.

Either run this macro to initialize the ontime method:

Sub StartOnTime()
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

or add

On Error Resume Next

to the sheet change event:

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

On Error Resume Next
Application.OnTime RunTime, "SaveAndCloseMe", , False
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub


Sorry about that,
Bernie
MS Excel MVP


"Jock" wrote in message
...
If only it was that easy.
All I did (after entering the code) was change the value in a cell and
tabbed out of it. The error message appears immediately.
--
tia

Jock


"Susan" wrote:

maybe because you're forcing it to run & 20 minutes hasn't elapsed yet?
or maybe not - just a thought.
:)
susan


"Jock" wrote in message
...
Bernie,
Copied & pasted your code.
However, I get an error message:
Run-time error '1004'
Method 'on time' of object '_Application' failed

What have I done wrong here?

--
tia

Jock


"Bernie Deitrick" wrote:

Jock,

Put this code into a regular codemodule:

Public RunTime As Date

Sub SaveAndCloseMe()
Application.DisplayAlerts = False
ThisWorkbook.Close True
Application.DisplayAlerts = True
End Sub


And put this code into the ThisWorkbook object's codemodule:

Private Sub Workbook_Open()
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.OnTime RunTime, "SaveAndCloseMe", , False
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

It will save and close the workbook after it hasn't been changed for 20
minutes.

HTH,
Bernie
MS Excel MVP


"Jock" wrote in message
...
Quite often, somone in our office has a certain file open to which
others
need access. The person who has it open hasn't made changes for, say 20
mins.
Is it possible to have the spreadsheet automatically save and close
after a
specified time period has elapsed?
--
tia

Jock