Long standing VBA has stopped functioning

[picklefactory]

Hi folks
Hoping someone can point me in the right direction please?
I have a spreadsheet I've used for years to track holidays with a tab for each month. I have a Workbook BeforeClose function that has always looked at the current month (NOW) and selected that month tab prior to saving and closing. It's always worked fine until this week. I get no error messages or any other issues with it but the WB now simply ignores that element and closes on whatever tab it was left on. In case the WB was corrupt I've tried copying out all the code (Only one module and the ThisWorkbook functions), saving as an xlsx, re-opening and replacing the code and re-saving as an xlsm, but that made no difference. As this workbook is shared across our network, I also have an OnTime function to automatically close it after 10mins of idleness so that no one can leave it open and lock out other users, that bit is still working fine.

This Workbook code is

Private Sub Workbook_Open()
    Call SetTimer
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Call StopTimer
    Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
  ByVal Target As Excel.Range)
    Call StopTimer
    Call SetTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Application.EnableEvents = True
    
    Call EditLog
    
    Sheets(Format(Now(), "mmmm")).Select
    
    Application.Quit
            
    
End Sub

and the module is

Dim DownTime As Date

Sub SetTimer()
    DownTime = Now + TimeValue("00:10:00")
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub
 
 Sub ShutDown()
 
    
    Application.DisplayAlerts = False
    
    Call EditLog
    
    Application.Quit
        
    
    End Sub

Function LastAuthor()
LastAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Function LastModified() As Date
   LastModified = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

Sub EditLog()


    With Worksheets("EDIT LOG")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(LastAuthor(), LastModified())
    End With

    Sheets(Format(Now, "mmmm")).Activate
    
End Sub

 

[picklefactory]

Where is the workbook saved, and is the Edit Log getting updated correctly? If so, I'd move the code to activate the sheet before the lines that update the edit log sheet.

Saved in a network folder Rory and now I come to check, no, the edit log is also not updating.

 

[picklefactory]

I've just tried F8 stepping through the code starting in the Shutdown sub, it runs through the Edit Log OK and updates that sheet but then gets stuck in an endless cycle around the OnTime sequence and won't drop out. I don't know if that's due to me stepping through or not. I think there must be something corrupt in my WB as the code worked fine for Georgiboy, so I can't see how it's the code, it must be something local to me. I'll try taking it off the network and saving on my pc

 

[picklefactory]

That made no difference either

 

[RoryA]

Why do you have:

    Application.EnableEvents = True

at the start of the BeforeClose event? It serves no purpose at all.

Is the timer code definitely working?

 

[picklefactory]

Why do you have:

    Application.EnableEvents = True

at the start of the BeforeClose event? It serves no purpose at all.

Is the timer code definitely working?

I added that earlier today based on a Google search, I found a fairly similar problem in the search and that was one of the suggested actions....... as you rightly pointed out.... it did bugger all!!! I'm currently making a complete copy of the sheet and just pasting everything over to a fresh WB to see if that helps? I'll check if the timer is working also

 

[picklefactory]

Yes... the timer just closed it down fine, so that bit works

 

[picklefactory]

Hmmm very strange. I copied everything over to a new WB placed on my desktop, the entire WB and the code, closed it down first time and it worked exactly as it should, asked me to save, and selected the current month tab. I opened it again and now it's failed again so something appears to be jamming the code on first run.

 

[RoryA]

Open the VB Editor, go to the Immediate Window (Ctrl+G) and type this in, then press Enter and see if it returns True or False:

?Application.EnableEvents

 

[picklefactory]

Open the VB Editor, go to the Immediate Window (Ctrl+G) and type this in, then press Enter and see if it returns True or False:

?Application.EnableEvents

True

 

[RoryA]

Was that after opening the workbook in question?