Thisworkbook.close closes work book but immediately reopens

[Kemidan2014]

Good morning. I have a spreadsheet that up to 12 people in my department will be using through out the day and I currently have a macro in it to close the workbook out after 15 minutes of inactivity because shareing the workbooks prevents some functionality of it.

with some of users when it closes it closes until the next time they open the file

WIth others when the Autoclose runs it closes the file but then immediately reopens the file.

what would be the reason for this?

in a seperate module:

'Make Declarations
Option Explicit
Dim killtime As Variant
Dim Procedurename As String

Sub Starttimer()
'Run Stoptimer macro
Stoptimer
'Define killtime
killtime = Now + TimeValue("00:30:00")
'Define Procedurename
Procedurename = "Closethisworkbook"
'when time is up Run killtime and procedurename
  Application.OnTime killtime, Procedurename

End Sub



Sub Stoptimer()
On Error Resume Next
'When timer is stopped it prevents killtime and procedurename from running
Application.OnTime killtime, Procedurename, , False

End Sub

Sub Closethisworkbook()
'Run Stoptimer Macro
Stoptimer
'Closes and saves workbook

Workbooks("Customer Complaint Tracker.xlsm").Close SaveChanges:=True

End Sub

in This workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Stoptimer

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

Starttimer

End Sub

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

Starttimer

End Sub

 

[6StringJazzer]

You have not declared killtime. Therefore it will be assumed to be local to each Sub and declared implicitly as Variant. When Stoptimer is called, killtime will be 0, so the timer will not actually be stopped. I am not certain this is causing your problem, but it is definitely a bug.

I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

You appear to be using it as a global variable so it needs to be declared at the top of the module:

Dim killtime As Double

 

[Kemidan2014]

THank you for your assessment, Just to clarify you are asking me to replace "Dim killtime As Variant" with "Dim killtime as Double" in Seperate module correct? not adding to "Thisworkbook" section

 

[6StringJazzer]

My mistake, you can ignore my post. When I scrolled down to read your post, the code scrolled and I did not see the declarations at the top of the module.