VBA Code Forcing File to Reopen when closed

[TheRobRush]

I have the following code in my modules. It works great, for what it was intended to do. (Keep a running clock for scheduling purposes, break meal penalties etc)

The issue arises if you have two copies of the file open at the same time.

When two files with this code in it ARE open, when you close one it will immediately REOPEN itself.

Any ideas?

Dim SchedRecalc As Date



Sub Recalc()

With Range("CG1")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub



Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

 

[Logit]

Posted in error.

 

[TheRobRush]

Only code in workbook close is hiding and unhiding of sheets.


Don't think issue is there. BUT just in case.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

' Hides all worksheets except the control panel before closing
' created by Rob Rush 2018

Application.ScreenUpdating = False
Worksheets("CONTROL PANEL").Visible = True
Worksheets("BOARD GENERATOR").Visible = xlSheetVeryHidden
Worksheets("Ticket Leads").Visible = xlSheetVeryHidden
Worksheets("Admissions Leads").Visible = xlSheetVeryHidden
Worksheets("UBO Leads").Visible = xlSheetVeryHidden
Worksheets("Booth 1").Visible = xlSheetVeryHidden
Worksheets("Booth 2").Visible = xlSheetVeryHidden
Worksheets("Booth 3").Visible = xlSheetVeryHidden
Worksheets("Booth 4").Visible = xlSheetVeryHidden
Worksheets("Will Call").Visible = xlSheetVeryHidden
Worksheets("Admissions Hosts").Visible = xlSheetVeryHidden
Worksheets("UBO Associates").Visible = xlSheetVeryHidden
Worksheets("Ambassadors").Visible = xlSheetVeryHidden
Worksheets("EET").Visible = xlSheetVeryHidden
Worksheets("Strollers").Visible = xlSheetVeryHidden
Worksheets("Dispatchers").Visible = xlSheetVeryHidden
Worksheets("Ambassador Leads").Visible = xlSheetVeryHidden
Worksheets("Tickets").Visible = xlSheetVeryHidden
Worksheets("UBO").Visible = xlSheetVeryHidden
Worksheets("Plaza").Visible = xlSheetVeryHidden
Worksheets("Entry").Visible = xlSheetVeryHidden
Worksheets("Dispatch").Visible = xlSheetVeryHidden
Worksheets("Rentals").Visible = xlSheetVeryHidden
Sheets("CONTROL PANEL").Select




End Sub

 

[Logit]

.
Try this :

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
Application.Quit
Workbooks("YourWBNameHere.xlsm").Close SaveChanges:=False
End Sub

 

[TheRobRush]

This worked perfect man. Took out the last two lines, because I don't want them not being prompted to save, but between your answer and *lightbulb* remembering to call on disable in my workbook close event I have it running perfectly now. Thanks a ton!

 

[Logit]

.
You are welcome.