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

 

[Kemidan2014]

Ah shoot thought i included sub names

all that was regarding to

Sub Starttimer()

 

[Siddharth Rout]

Try this

Code in ThisWorkbook

Option Explicit

Dim killtime As Variant

Private Sub Workbook_Open()
    SetNewTime
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.OnTime EarliestTime:=killtime, Procedure:="Closethisworkbook", Schedule:=False
    SetNewTime
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.OnTime EarliestTime:=killtime, Procedure:="Closethisworkbook", Schedule:=False
    SetNewTime
End Sub

Private Sub SetNewTime()
    killtime = Now + TimeValue("00:02:00")
    Application.OnTime killtime, "Closethisworkbook"
End Sub

Code in a Module

Option Explicit

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

If you face any issues or any errors, let me know and we will take it from there.

 

[Micron]

Sorry, I had no idea post 6 was directed to me so I didn't respond, expecting you were going to try something else. Nor could I see how that shows you moved the close statement to the other sub. Apparently you can't because it's also linked to a button click event? Sounds like you put a workbook close event on a button - that would just complicate things and isn't necessary. Hopefully Siddharth's code will work for you.

 

[Kemidan2014]

Micron, you clued me in on pausing a sub, which i think is very useful, even aware of the dangers of it

Sid, I have tested your code however i am getting an error every time i select a cell, thoughts?

 

[Siddharth Rout]

I just tested the code and I do not get that error. Possible to see your workbook?

 

[Kemidan2014]

I can try but not sure the best way to that. i have Xl2bb addin if thats what your meaning

 

[Siddharth Rout]

I can try but not sure the best way to that. i have Xl2bb addin if thats what your meaning

Add-in? Did I miss you mentioning that somewhere or you actually missed mentioning that :D

Obviously then the code that I gave you will not work that way. You will have to tweak the code accordingly. Can you exactly explain what you are trying to achieve?

 

[Kemidan2014]

i didnt mention it because i thought xl2bb is unrelated. its whats used to copy cell ranges with conditionformatting and formulas included into this forum so that you didnt have to post the entire book.

What I am trying to achieve is

i want the work book and only the workbook not other open work books to close if they are not using it after 15minutes. this is to release it. We tried to use the Shareing feature but we didnt like how it behaved.

then WHEN finally closes after the timer is up. i wanted it to call another macro to run before it closes. we all have a bad habit to work in the file and due to our job having to get up immediately to goto the floor. the idea was to update the master sheet and close it so the user didnt have to reenter information.

 

[Micron]

Add-in? Did I miss you mentioning that somewhere or you actually missed mentioning that :D

It's a forum add-in and will have no bearing on the code - see icon on posting toolbar. I don't recall ever using it.
What was suggested is probably a link to a drop box somewhere.

 

[Siddharth Rout]

i didnt mention it because i thought xl2bb is unrelated. its whats used to copy cell ranges with conditionformatting and formulas included into this forum so that you didnt have to post the entire book.

What I am trying to achieve is

i want the work book and only the workbook not other open work books to close if they are not using it after 15minutes. this is to release it. We tried to use the Shareing feature but we didnt like how it behaved.

then WHEN finally closes after the timer is up. i wanted it to call another macro to run before it closes. we all have a bad habit to work in the file and due to our job having to get up immediately to goto the floor. the idea was to update the master sheet and close it so the user didnt have to reenter information.

Please download this file and try this for me.

Book1.xlsm

Shared with Dropbox

www.dropbox.com

This is a temp link and will be deleted after 5 days.

Let me know if you still get any errors...