Home > Enterprise >  File keeps re-opening by itself
File keeps re-opening by itself

Time:02-18

Hello One of the VP where I work made a workbook that has the below macro in it. For some reason after I open the file and close it on my own the file re-opens itself every so often. Is this because the timer in the workbook is set to reset its closing process? I am not very well versed in VBA yet so that may not be even close to what the Sub Reset is doing. Note this apparently only happens to me and not anyone else and we have no idea why. only VBA experience I have is like making workbooks that don't close as pranks or making time stamps or color counting formulas.

Dim xTime As String
Dim xWB As Workbook
 
Private Sub Workbook_Open()
'Updated by Extendoffice 2019/1/20
    On Error Resume Next
    xTime = "00:30:00"
    Set xWB = ActiveWorkbook
    If xTime = "" Then Exit Sub
    Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    If xTime = "00:30:00" Then Exit Sub
    Reset
End Sub
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    If xTime = "" Then Exit Sub
    Reset
End Sub
 
Sub Reset()
    Static xCloseTime
    If xCloseTime <> 0 Then
        ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , False
    End If
    xCloseTime = Now   TimeValue(xTime)
    ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , True
End Sub

CodePudding user response:

The timer is a async process so it will keep running in background you can reset it on closing of the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Reset
End Sub

CodePudding user response:

The workbook is being reopened when Application.OnTime calls the SaveWork1 that is attached to it. Static xCloseTime is storing the time that the SaveWork1 is scheduled to be ran. The time is being stored so that the Application.OnTime event can be cancelled.

I believe that you are the only one being affected because you are the only one that dabbles in VBA. VBA errors may cause Static variables to lose their values. When Static xCloseTime the SaveWork1 is rescheduled.

  • Related