How to stop a loop once the spreadsheet has closed

[paulym666]

I have a spreadsheet that's used by many people (who all want to make changes at roughly the same time each day) - too many for sharing to work without a ridiculous number of conflicts constantly cropping up. So it's not shared and we have to trust users to go in, make changes, save and close quickly.

But, of course, sometimes someone keeps it open blocking others from using it and usually Excel just says it's locked for editing by "another user".

I wrote a little VBA designed to remind the user every 5 minutes to close the file if they've finished with it. It works, but (inexplicably) it keeps working even after the spreadsheet is closed - it just opens it again and displays the reminder (defeating the object).

So, my question is: how can I force the VBA loop to quit when I close the spreadsheet?

My code is:

Sub Macro2()
alertTime = Now + TimeValue("00:05:00")
Application.OnTime alertTime, "my_Procedure"
End Sub


Sub my_Procedure()
MsgBox "Please close the spreadsheet if you have finished your work"
Call Macro2
End Sub

Thanks

 

[Kamolga]

I have never met such an issue. What about not enabling events before closing? You can 2x click "this workbook" below the sheets in left column of VBA and paste this

[LEFT][COLOR=#303336][FONT=Consolas]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
End Sub
Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub[B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT][/COLOR][/LEFT]

 

[Jaafar Tribak]

Try this :

Option Explicit

Dim alertTime As Double

Sub Macro2()
    alertTime = Now + TimeValue("00:05:00")
    Application.OnTime alertTime, "my_Procedure", , Schedule:=True
End Sub

Sub my_Procedure()
    MsgBox "Please close the spreadsheet if you have finished your work"
    Call Macro2
End Sub

Sub Auto_Close()
    Application.OnTime alertTime, "my_Procedure", , Schedule:=False
End Sub

 

[paulym666]

I have never met such an issue. What about not enabling events before closing? You can 2x click "this workbook" below the sheets in left column of VBA and paste this

[LEFT][COLOR=#303336][FONT=Consolas]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
End Sub
Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub[B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT][/COLOR][/LEFT]

Thanks, but this didn't solve the problem - the spreadsheet kept reopening.

 

[paulym666]

Try this :

Option Explicit

Dim alertTime As Double

Sub Macro2()
    alertTime = Now + TimeValue("00:05:00")
    Application.OnTime alertTime, "my_Procedure", , Schedule:=True
End Sub

Sub my_Procedure()
    MsgBox "Please close the spreadsheet if you have finished your work"
    Call Macro2
End Sub

Sub Auto_Close()
    Application.OnTime alertTime, "my_Procedure", , Schedule:=False
End Sub

This worked - thanks for your help