Time out macro that kicks to another worksheet

[MFogarty]

I wanted to make a timeout macro that will kick users to the main worksheet if they are idle for too long. I have read the time out macro post and am still a little in the dark on kicking to another page rather than closing. Any help would be great! Thanks!

 

[erik.van.geit]

Hi, MFogarty,

Could you post the link to the timeout-thread: so we would know we talk about the same code.

kind regards,
Erik

 

[Nimrod]

WHAT IS DOES
The following code starts a timer when you goto any sheet exept for sheet1 and sheet2.
This timer will, if there is no sheet change for 30 seconds, trigger macro that will take you back to a defualt sheet eg. Sheet1


THINGS TO CONFIGURE

Public Const ExemptSheets As String = "Sheet1,Sheet2"
Public Const DeFaultSheet As String = "Sheet1"
Const TimeLag As Variant = "00:00:30"

ExemptSheets are sheets that you do not want the timer to run on , currently timer does not run when your on sheets named "Sheet1" or "Sheet2"

DeFaultSheet is the sheet that you want to go back to when timer run out , eg. currently set for "Sheet1"

TimeLag is the time to wait for user to change a sheet value hh:mm:ss , eg currently set at 30 seconds

CAUTION
Make sure you run "StopTimer" before you attempt to change anysettings. Or you could end up in a very nasty loop. In fact the best way to configure settings is to , 1. exit worksheet ,2. re-open worksheet with macros disabled , 3. make changes , 4. re-open book with macro's enabled.



PLACE THIS CODE IN A MODULE

Public dNext As Variant
Public Const ExemptSheets As String = "Sheet1,Sheet2"
Public Const DeFaultSheet As String = "Sheet1"
Const TimeLag As Variant = "00:00:30"

Public Sub StartTimerAgain()
    'calculate when code should run again
    dNext = Now + TimeValue(TimeLag)
    
    'set on time event to trigger running
    Application.OnTime dNext, "DoAction"
    
End Sub

Public Sub DoAction()

    Sheets(DeFaultSheet).Select

    'start timer again
    Call StartTimerAgain
End Sub

Public Sub StopTimer()

' time has not been used yet if dNext empty
If IsEmpty(dNext) Then Exit Sub

    On Error Resume Next
    'this turns off the on time event
    Application.OnTime dNext, "StartTimerAgain", schedule:=False
End Sub

PLACE THIS CODE IN the specific code module called THISWORKBOOK

'use this to turn off the Application on time event otherwise workbook will reopen
Sub Auto_Close()
  StopTimer
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Call StopTimer
    
    If InStr(1, ActiveSheet.Name, ExemptSheets) = 0 Then
      Call StartTimerAgain
    End If 'not exempt

End Sub


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

    Call StopTimer
    
    If InStr(1, ActiveSheet.Name, ExemptSheets) > 0 Then
       Call StartTimerAgain
    End If 'not exempt

End Sub

 

[MFogarty]

Subscript out of Range Error

I am getting a subscript out of range error which I do not understand. The default sheet is set to sheet 12 chich is my main worksheet. Any advice? Thanks by the way for your help!

 

[MFogarty]

To expand on that last post as i may be unclear. The error is occuring in the Public Sub Do action. Where it selects the sheet Default.

 

[erik.van.geit]

MFogarty,

with this kind of problems it's good to extract the code to a new workbook
can you make this work?

Public Const DeFaultSheet As String = "whatevername"

Public Sub DoAction()
    Sheets(DeFaultSheet).Select
End Sub

kind regards,
Erik

 

[Nimrod]

Could you please post to show how you've changed the following line ...


Public Const DeFaultSheet As String = "Sheet1"


Could you also post the name of the Target Sheet ?

This 2 MUST match perfectly with the sheet name .... So please highlight it's Sheet Tab and post it here as well ..

 

[MFogarty]

Dumb Mistake...I was trying to use the sheet number instead of the name. Thanks again for great code!

 

[erik.van.geit]

three ways to refer to sheets
all have their "pro and contra"

Sub activate_sheet_at_the_left()
    'ignoring names
    Sheets(1).Activate
End Sub

Sub activate_sheet_named_Sheet1()
    'renaming this sheet will result in an error
    Sheets("Sheet1").Activate
End Sub

Sub activate_sheet_coded_as_Sheet1()
    'codename given when created
    Sheet1.Activate
End Sub

this could clarify a bit
Erik