I have a workbook that refuses to close if any other workbooks are open at the same time. This workbook has a running clock in it. Here are the macros that run this clock:
Private Sub Workbook_Open()
'other code
Call Recalc
'other code
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
ThisWorkbook.Save
End Sub
'The following is in a module
Sub Recalc()
Dim ScheduleRecalc As Date
Dim wb As Workbook: Set wb = Workbooks.Item("MyWorkbook.xlsm")
Dim ws As Worksheet: Set ws = wb.Sheets("MyWorksheet")
ws.Range("E1").Value = Format(Now, "dd-mmm-yy")
ws.Range("E2").Value = Format(Time, "hh:mm:ss AM/PM")
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
I got this code online and I frankly don't really understand how it works, but it keeps a clock ticking steadily on one of the spreadsheets, and I need it to continue doing this. But now, when there are other workbooks open, this workbook will not close. Not only that, but if I try to close it, it runs through the code in the Workbook_Open sub!
Thoughts? Is there another way to run a clock in a workbook that I could try instead? Is there a loop somewhere in here that I'm not closing correctly? Help, please!
Bookmarks