Calling a sub before thisworkbook.close

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
229
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
'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
 
Ah shoot thought i included sub names

all that was regarding to

Sub Starttimer()
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try this

Code in ThisWorkbook

VBA Code:
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

VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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?
1659361581526.png


1659361548069.png
 
Upvote 0
I just tested the code and I do not get that error. Possible to see your workbook?
 
Upvote 0
I can try but not sure the best way to that. i have Xl2bb addin if thats what your meaning
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.


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

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

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top