Calling a sub before thisworkbook.close

[Kemidan2014]

I have a macro that i foudn through googling that DOES work but I got a complaint from the other users of the sheet that when it runs it closes ALL spreadsheets
My thought was change the way the workbook is being reference for closing.

Before when i used "thisworkbook.close" "updatemasterAACT" sub ran successfully before closing but when i SWITCH to "workbooks("insertnamehere").close now "updatemasterAACT" errors out because the work book is closing before it finishes running (atleast thats my assessment based on where the macro is saying its failing)

below is the code NOTE: "Workbooks("Customer Complaint Tracker").Close Savechanges:=True" was PREVIOUSLY "Thisworkbook.close Savechanges:=True"

the only reason i have this timer is because people would leave the file open to work on other files preventing others from accessing it.

is there a way i can coordinate the running of the macros so that they fall in order and dont try to run over each other?

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

Sub Starttimer()
'Run Stoptimer macro
Stoptimer
'Define killtime
killtime = Now + TimeValue("00:15: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
UpdatemasterAACT
Workbooks("Customer Complaint Tracker").Close Savechanges:=True

End Sub

 

[Micron]

If you will never call UpdatemasterAACT at any other time, then move the close statement to that sub? Otherwise do you know for sure which sub is raising the error? If not, use error trapping and create a message box that shows the error number, description and the name of the sub (in Closethisworkbook and in UpdatemasterAACT). You could also try calling a pause sub/function for n seconds between UpdatemasterAACT and Workbooks closing lines. However that is less desirable IMO because 1) you have to guess at for how long to pause; 2) network efficiency can mean that some days the pause isn't long enough.

 

[Siddharth Rout]

Quick question. Why 15 minutes? What if the user is still working on the worksheet beyond 15 mins?

Do you think something like this may help?

Code in a Module

Option Explicit

Public killtime As Variant

Sub Closethisworkbook()
    ThisWorkbook.Close Savechanges:=True
End Sub

Code in ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    killtime = Now + TimeValue("00:02:00")

    Application.OnTime killtime, "Closethisworkbook"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    '~~> User selected a cell. Extend the time
    Application.OnTime EarliestTime:=killtime, Procedure:="Closethisworkbook", Schedule:=False
 
    killtime = Now + TimeValue("00:02:00")
    Application.OnTime killtime, "Closethisworkbook"
End Sub

I set the time as 2 minutes for testing and it works. The logic here is if the user doesn't select any cell in the worksheet for 2 minutes (you can change that) then the user is not working on it.

 

[Kemidan2014]

Micron to answer your question, the sub "updatemasterAACT" is also an assigned macro tied to a button in one of my tabs, the intention why i added it to the close routine was if someone was working on it and had to leave thier desk it would copy the info over to a master spreadsheet that they were working on. it just has to do with how im using particular tabs to call up specific information with out filtering the data in the main sheet. i wanted to keep the master sheet out of hands of the users. i know in the end i can just remove the call for the sub and it will work fine, just trying to make things easy.

I can tell you that it was erroring out on "updatemasterAACT" sub when the timer reached 0. and for some reason it errored out simply trying to unprotect the sheet which is one of the first lines in the sub. so Id have to guess that its erroring because there's no longer a sheet to unlock because the close command did its magic first.

i would like to try the pause line, ive never used that. so i will give it a try and let you all know if that works

Sid;
Ah yes, i have other subs in "thisworkbook" for changes that restart the timer for example when they change tabs, edit a cell, etc its set up that if the workbook sees zero activity for 15 minutes

 

[Siddharth Rout]

Sid;
Ah yes, i have other subs in "thisworkbook" for changes that restart the timer for example when they change tabs, edit a cell, etc its set up that if the workbook sees zero activity for 15 minutes

Then it is pretty straight forward. Did you try the approach that I suggested? The above code works without crashing the workbook.

 

[Kemidan2014]

Gave it a shot

however THIS time the updatemaster function worked the way it supposed to but gave me a new error. instead of needed to debug UpdatemasterAACT macro the location switch to the autoclose macro

at this point if i hit end or stop the macro it just leaves the sheet open

 

[Siddharth Rout]

Gave it a shot

however THIS time the updatemaster function worked the way it supposed to but gave me a new error. instead of needed to debug UpdatemasterAACT macro the location switch to the autoclose macro

at this point if i hit end or stop the macro it just leaves the sheet open

If this message is for me then this is not what I suggested...

 

[Kemidan2014]

Sorry Sid that was for Micron, here is code i have in :"THis workbook"

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

 Starttimer

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Stoptimer
ThisWorkbook.Save
End Sub

and then this is 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:01: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
UpdatemasterAACT

 Application.Wait (Now + TimeValue("00:00:05"))

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

End Sub

 

[Siddharth Rout]

Sorry Sid that was for Micron, here is code i have in :"THis workbook"

End Sub[/CODE]

You still have not tried the code that I gave. If I give you a fresh code, are you willing to try it? If not then I will humbly step back.

 

[Kemidan2014]

Sid, if i am following your line of thought

i should change

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

TO this

    Application.OnTime EarliestTime:=killtime, Procedure:="Closethisworkbook", Schedule:=False

 

    killtime = Now + TimeValue("00:02:00")

    Application.OnTime killtime, "Closethisworkbook

COrrect??