Dave,
Macros are allowed to run on opening.
I ran the sub and get the following error:

"Compile Error:
Invalid use of Me keyword"

Public Sub CloseMe() highlights in yellow


"Dave Peterson" wrote:

You have to reopen the workbook to get the workbook_Open event to fire (or run
it manually).

And make sure you allow macros to run when you open the workbook.

Tony S. wrote:

Hi Chip/Dave.

I have a real need for this routine, but I copied and pasted it exactly as
written; saved the file and nothing happens. Any ideas?

Here is the code as pasted into ThisWorkbook:

Option Explicit
Option Compare Text

Private RunWhen As Double
Private Const C_TEST_OPEN_SECONDS = 5

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
End Sub

Private Sub Workbook_Open()
RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True
End Sub

Public Sub CloseMe()

Dim IWSH As IWshRuntimeLibrary.WshShell
Dim Res As Long
Set IWSH = New IWshRuntimeLibrary.WshShell

Res = IWSH.Popup(Text:="Your time is up. Keep open?", _
secondstowait:=3, Type:=vbYesNo + vbDefaultButton2)
If (Res = -1) Or (Res = vbNo) Then
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
Me.Close savechanges:=True
End If

RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True

End Sub

"Chip Pearson" wrote:

The code I posted was incomplete. Use the following code in the ThisWorkbook
module:


Option Explicit
Option Compare Text


Private RunWhen As Double
Private Const C_TEST_OPEN_SECONDS = 5


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
End Sub

Private Sub Workbook_Open()
RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True
End Sub

Public Sub CloseMe()

Dim IWSH As IWshRuntimeLibrary.WshShell
Dim Res As Long
Set IWSH = New IWshRuntimeLibrary.WshShell

Res = IWSH.Popup(Text:="Your time is up. Keep open?", _
secondstowait:=3, Type:=vbYesNo + vbDefaultButton2)
If (Res = -1) Or (Res = vbNo) Then
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
Me.Close savechanges:=True
End If

RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True

End Sub




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)




"Chip Pearson" wrote in message
...
Marilyn,

In VBA, go to the Tools menu, choose References, and check "Windows Script
Host Object Model". The paste all the code below in to the ThisWorkbook
code module. Change the value of C_TEST_OPEN_SECONDS to the number of
seconds that the workbook should stay open without any user interaction.

See also http://www.cpearson.com/excel/TimedClose.htm



Option Explicit
Option Compare Text


Private LastTime As Double
Private RunWhen As Double
Private Const C_TEST_OPEN_SECONDS = 600 '<<< CHANGE


Private Sub Workbook_Open()
RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , False
RunWhen = Now + TimeSerial(0, 0, C_TEST_OPEN_SECONDS)
Application.OnTime RunWhen, "ThisWorkbook.CloseMe", , True
End Sub

Public Sub CloseMe()

Dim IWSH As IWshRuntimeLibrary.WshShell
Set IWSH = New IWshRuntimeLibrary.WshShell
If IWSH.Popup(Text:="Your time is up. Keep open?", _
secondstowait:=3, Type:=vbYesNo + vbDefaultButton2) = -1 Then
Me.Close savechanges:=True
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Marilyn" wrote in message
...
Hello I have a workbook that is in a network and about 20 people have
access
to it . the problem is that sometimes they forget to close the file and
no
one else can use it. Is there a way to put a time limit on a workbook,
so
that the book will automatically close after 5 minutes - giving a
warning to
the user before closing. THANKS






--

Dave Peterson