2nd Close Workbook after inactivity

**Dreamwine59** · 05-17-2007, 11:48 AM

I read this post "VBA Close Workbook after Inactivity"and got very excited. It is something I can really use.
So I pasted all the code from RoyUK in my spreadsheet and set the time parameters to 1 minute to test it. My spreadsheet is still open, so I am wondering what I did wrong. Were some of the procedures supposed to be combined into one? I put them in a module, and listed each one of them seperately as in the example.

**Simon Lloyd** · 05-17-2007, 01:33 PM

Deb, Roy(uk) is from a different forum, i have supplied a link to this post to him as we havent seen the code and cannot comment especially seeing that you didn't provide any code for us to work with

**royUK** · 05-17-2007, 02:53 PM

here's a link to the earlier answer.

I think you have the code in the wrong places.

This should be in a standard module

'---------------------------------------------------------------------------------------
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   :  Clck here for more examples and Excel Consulting
' Purpose   : Place in a standard module
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit

Public EndTime
Sub RunTime()
    Application.OnTime _
            EarliestTime:=EndTime, _
            Procedure:="CloseWB", _
            Schedule:=True
End Sub

Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

Place this in the Workbook_Open event

Option Explicit

Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:05:00")
    RunTime
End Sub

Place this in each worksheet to detect any changes

Private Sub Worksheet_Change(ByVal Target As Range) 
    If EndTime Then 
        Application.OnTime _ 
        EarliestTime:=EndTime, _ 
        Procedure:="CloseWB", _ 
        Schedule:=False 
        EndTime = Empty 
    End If 
    EndTime = Now + TimeValue("00:10:00") 
    RunTime 
End Sub

**Dreamwine59** · 05-17-2007, 03:50 PM

thanks to Simon and RoyU. I will work with it from here. The Clarification helps since I am new to VBA

**royUK** · 05-18-2007, 03:33 AM

Here's an example workbook that might help

**boylejob** · 05-18-2007, 11:05 AM

RoyUK,

Thanks you RoyUK. I have been watching this post and the piece of code you put out there is wonderful. Neatest thing I have seen in a while. I put it in a workbook of mine and it works great. I appreciate you sharing with us.

Deb,

Thanks for putting that question out there. I am not sure exactly where I will use this application, but it is certainly nice to have. Hope you got the code to work on your end.

**WinterGreen** · 01-28-2013, 11:40 AM

Hi, I have copied this code as instructed above, but I am getting errors? What time values to I have to change in the above code?

**Simon Lloyd** · 01-28-2013, 12:02 PM

Change this

Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

to

Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Save
        .Close
    End With
End Sub

**Pierce Quality** · 08-20-2013, 11:03 AM

I was searching for for some code for closing the workbook after inactivity and came across this thread, it was very helpful and the code worked great, thank you.

**mattdoubleyoo** · 09-04-2013, 09:54 AM

hey guys thanks for this. Quick question though, where do I find the "Workbook_Open" event?

**EtiennePal** · 09-29-2017, 03:25 AM

Hi Guys

This is really a great piece of Code.
I would like to use it on multiple sheets.
I am getting errors if i paste this code in the sheets i want to use.

One particular sheet, i don't want to close but I want to call another macro.
Can it be done?

I am a self taught and fairly new in VBA.

Your help is much appreciated.