Auto close workbook and open another after inactivity

[dplumlee12]

I have a menu workbook with macro buttons that call different workbooks. Call the menu workbook (switchgear.xlsm). Switchgear contains 2 buttons. Button 1 closes switchgear and opens Book1. Button 2 closes switchgear and opens book 2.

If book1 is open and nothing has changed for 3 minutes I would like it to close and open switchgear.xlsm. I do not want it to just close in 3 minutes... I want it to close after 3 minutes of inactivity.

I have search all over and tried and tried to achieve this without success...

I have been able to get the timer to work but it gets caught in a loop. If I open book1 and close it in less than a min then 3 minutes later out of nowhere the code tries to run???

Secondly when this happens I see serveral book1 vba projects loaded in the editor window???

Please HELP!!!!

 

[JoeMo]

I have a menu workbook with macro buttons that call different workbooks. Call the menu workbook (switchgear.xlsm). Switchgear contains 2 buttons. Button 1 closes switchgear and opens Book1. Button 2 closes switchgear and opens book 2.

If book1 is open and nothing has changed for 3 minutes I would like it to close and open switchgear.xlsm. I do not want it to just close in 3 minutes... I want it to close after 3 minutes of inactivity.

I have search all over and tried and tried to achieve this without success...

I have been able to get the timer to work but it gets caught in a loop. If I open book1 and close it in less than a min then 3 minutes later out of nowhere the code tries to run???

Secondly when this happens I see serveral book1 vba projects loaded in the editor window???

Please HELP!!!!

Try this. I define inactivity as no activation/deactivation of a sheet, no calculation, no change to any cell, no selection change.
This code goes into a standard module for Book1.

Public Const runWhat = "CheckTime"
Public Const ckInt = 3 ' minutes
Public runWhen As Double
Public T As Double

Sub CheckTime()
runWhen = Now + TimeSerial(0, ckInt, 0)
If Now - T >= ckInt / (24 * 60) Then
    Call StopTimer
    Workbooks.Open Filename:=ThisWorkbook.Path & _
        Application.PathSeparator & "switchgear.xlsm"
    ThisWorkbook.Saved = True
    ThisWorkbook.Close
Else
    Application.OnTime earliesttime:=runWhen, procedure:=runWhat, schedule:=True
End If

End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=runWhen, procedure:=runWhat, schedule:=False
End Sub

This code goes into a Thisworkbook module for Book1.

Private Sub Workbook_Open()
T = Now
Call CheckTime
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
T = Now
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
T = Now
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
T = Now
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
T = Now
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
T = now
End Sub

 

[dplumlee12]

Do you know why my VBA project window shows a multiple instances of the same project? everytime I open and close the same workbook another duplicate copy of the VBA project from that workbook opens and stays in the project window?

 

[dplumlee12]

Also, what if switchgear is on a different drive?

 

[Sam_D_Ben]

Hey Joe,

That first code ..Superb !

Muz

 

[JoeMo]

Do you know why my VBA project window shows a multiple instances of the same project? everytime I open and close the same workbook another duplicate copy of the VBA project from that workbook opens and stays in the project window?

No idea. Have you tried closing and restarting Excel or rebooting your computer?

 

[JoeMo]

Also, what if switchgear is on a different drive?

Then you need to replace Thisworkbook.Path with the path to the workbook switchgear.

 

[dplumlee12]

Thanks for all your help... I found out that there is something with having a com add-in installed. Removed the add-in and the vba projects close correctly.

 

[dplumlee12]

Ya I finally figured that out... Thanks a ton... works perfectly.

 

[dplumlee12]

Well I thought it was working but seems that the timer is running about 6 min and keeps executing the open swithgear even while working in spreadsheet... Any ideas?