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