I will be very grateful if someone can suggest to me the neatest way t
achieve the following:

I want a visible warning and/or audible alarm to popup after fiv
minutes of inactivity within a workbook session.

The reason for this is that a number of users need to enter data into
'common' index folder, often users will have finished entering thei
data but have left the workbook open, consequently forbidding writ
access to anyone else.

Many thanks
Le

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

The basic code would be found at Chip Pearson's site
http://www.cpearson.com/excel/ontime.htm

You would cancel the ontime in the workbook level selection change event and
schedule a new one 5 minutes hence.

http://www.cpearson.com/excel/events.htm

if you are not familiar with events.

John Walkenbach has code for playing a sound:
http://j-walk.com/ss/excel/tips/tip59.htm



--
Regards,
Tom Ogilvy

evillen wrote in message
...

I will be very grateful if someone can suggest to me the neatest way to
achieve the following:

I want a visible warning and/or audible alarm to popup after five
minutes of inactivity within a workbook session.

The reason for this is that a number of users need to enter data into a
'common' index folder, often users will have finished entering their
data but have left the workbook open, consequently forbidding write
access to anyone else.

Many thanks
Len


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I appreciate your advice Tom.

I looked at the sources you suggested and created the code below
Whenever I attempt to get the macros to run, I get a warning telling m
that "...!The_Sub' cannot be found".

I am sorry if this is a silly syntax problem, but I am pulling my hai
out over this!

Thanks
Len


Public RunWhen As Double
'Public Const cRunIntervalSeconds = 120 ' two minutes
'Public Const cRunWhat = "The_Sub"
Const cRunIntervalSeconds = 10
Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Msg = "Please close this workbook"
MsgBox (Msg)
StartTimer

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

I have been looking at this again, and I believe that the problem ha
something to do with my Public Const declarations - the previous cod
was altered to make the constants local to the StartTimer process.

" get a warning that Constants are not allowed as Public members o
object modules". Please see code example below:
---------------------------------------------------------------------------

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Msg = "Please close this workbook"
MsgBox (Msg)
StartTimer

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Put all your code in a general module. ( in the VBE, insert=Module)

Then call the appropriate code from the workbook level selectionchange event
in the Thisworkbook module.

the public variable will be visible to the thisworkbook module as well. (you
can assign them a value and read them from there if you need to).

--
Regards,
Tom Ogilvy

evillen wrote in message
...

I have been looking at this again, and I believe that the problem has
something to do with my Public Const declarations - the previous code
was altered to make the constants local to the StartTimer process.

" get a warning that Constants are not allowed as Public members of
object modules". Please see code example below:
--------------------------------------------------------------------------
-

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Msg = "Please close this workbook"
MsgBox (Msg)
StartTimer

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/