Returning Home

[JasonBing]

Is there a way to get a workbook to activate a certain worksheet, say Sheet1 "A1" after a set period of inactivity?


Thanks

Jason Bing


EDIT: I know you can get a workbook to save and close after inactive time. But I need it to return to a home page?

 

[JasonBing]

yep don't know how to post the screenshot


Sorry about that. Hope I am making sense

Regards

Jason Bing

 

[Michael M]

Ok, slightly different approach
This in a standard module

Option Explicit
Public LastActivityTime As Date
Sub Check_Inactivity2()
    Const Inactivity_Delay As Date = #12:01:00 AM# ' currently set for one minute
    If LastActivityTime + Inactivity_Delay < Now() Then
        Sheets("Sheet1").Activate
        Range("A1").Select
    Else
        Application.OnTime LastActivityTime + Inactivity_Delay, "Check_Inactivity2"
        End If
    End Sub

This in the "ThisWorkbook" module

Option Explicit
Private Sub Workbook_Open()
    LastActivityTime = Now()
    Check_Inactivity2
    End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    LastActivityTime = Now()
    End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    LastActivityTime = Now()
    End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    LastActivityTime = Now()
    End Sub

 

[Yongle]

@JasonBing

- did you save, close and reopen the workbook?
- opening the workbook is the "trigger" which gets things running

 

[JasonBing]

Thanks for the reply’s. Will test when I get back to the office. Yes I did save exit and RE open. Thanks for the input.

Regards

Jason bing

 

[JasonBing]

The code worked beautifully on the test workbook and on the master project. Little problem though, it only works once!

To get it to work again you have to close the workbook and reopen it. Then it will work again, once.

Awesome bit of code though I am impressed, I did some testing also and it doesn't seem to make a difference if autosave is on or off still works

Thanks again for your help. This id definitely above my pay grade. I am in awe

Regards

Jason Bing

 

[Michael M]

If you want it to work all the time it would mean the code would be working ALL the time, but it can be done.
Is that what you want ??

 

[JasonBing]

It would be great if it worked all the time.

Thank You fir your time

Regards

Jason Bing

 

[Michael M]

Ok, replace the Check_Inactivity2 code with this one

Option Explicit
Public LastActivityTime As Date
Sub Check_Inactivity2()
    Const Inactivity_Delay As Date = #12:01:00 AM# ' currently set for one minute
    If LastActivityTime + Inactivity_Delay < Now() Then
        Sheets("Sheet1").Activate
        Range("A2").Select
        Range("A1").Select
     Application.OnTime LastActivityTime + Inactivity_Delay, "Check_Inactivity2"
Else
        Application.OnTime LastActivityTime + Inactivity_Delay, "Check_Inactivity2"
        End If
    End Sub

 

[JasonBing]

Thanks for the reply. Testing the code everything seemed to work great. I would navigate to another sheet, leave it and it returned to sheet 1, after the allotted time. However if the workbook is sitting on sheet 1, after the allotted time Excel becomes non responsive.


Thanks again

Jason Bing