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]

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?

May have gotten out of sync. Run the StopTimer sub (open VBE and run it alone). Close both workbooks then open Book1 and do nothing for the inactivity time. This should reset the timer.

 

[JoeMo]

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?

The timer may have gotten out of sync. Run the sub StopTimer by itself from the VBE. Then close both workbooks and re-open Book1 and do nothing for the inactivity period you set. That should reset the timer.

 

[dplumlee12]

So here is what the code is doing. When I open book1 and close book1 returning to switchgear the code continues to run and when the timer is up the code opens book1 and then tries to reopen switchgear. Is there a line of code that can be put in to discontinue the code on exit?

 

[JoeMo]

So here is what the code is doing. When I open book1 and close book1 returning to switchgear the code continues to run and when the timer is up the code opens book1 and then tries to reopen switchgear. Is there a line of code that can be put in to discontinue the code on exit?

Add this code to the Thisworkbook module for Book1:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub