Shared workbook inactivity warning

  • Thread starter Thread starter evillen
  • Start date Start date
E

evillen

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
 
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, procedure:=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, _
procedure:=cRunWhat, schedule:=False
End Su
 
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, procedure:=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, _
procedure:=cRunWhat, schedule:=False
End Su
 
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).
 
Back
Top