Code to automatically close an Excel spreadsheet

[JoAnn]

I'm using code I found in an earlier email thread to save/close a workbook
after 1 minute, after prompting the user to see if he/she is still there
(resets the timer if Yes is clicked).

The code works fine as long as the user has exited out of a data field. But
if the user has entered data in a field but hasn't clicked out of it, the
wkbook stays open as it would if the code weren't there.

1) How can I force it to exit the field, save whatever has been entered &
close?

2) I would also like to add a countdown message to the "Are you there?"
prompt (i.e. "If you do not click Yes, this workbook will close in <#
seconds>"). How can I do that?

The code being used is below:

In Thisworkbook:

Private Sub Workbook_Open()
Application.OnTime Now() + TimeValue("00:01:00"), "CloseMe"
End Sub

In a New Module:

Public Sub CloseMe()
Dim SH As IWshRuntimeLibrary.WshShell
Dim Res As Long

Set SH = New IWshRuntimeLibrary.WshShell
Res = SH.Popup(Text:="Are you still there?", secondstowait:=3, _
Title:="Doc Tracking List Check", Type:=vbYesNo)
If Res = vbYes Then
Application.OnTime Now() + TimeValue("00:01:00"), "CloseMe"
Else
ThisWorkbook.Save
ThisWorkbook.Close
End If
End Sub

 

[Jim Thomlinson]

Macros do not fire while you are in edit mode. There is no way to force the
user out of edit mode. So the short answer is there is no easy way to do what
you have asked...

 

[JoAnn]

Thanks for your response.

You said there's no easy way ... is there any way at all?

I would even settle for it closing without saving just to prevent the file
from remaining open indefinitely on someone's PC. Losing a little data would
be better than tying up the worksheet & preventing other users from accessing
it.

Since the entries tie into a related action outside of Excel, we would be
able to determine that an entry is missing & rectify it after the fact. The
only thing I wouldn't want to do is quit Excel app without warning, since
they often use Excel for other things.