Auto-Save issue

[pella88]

Hello,

I have an issue with my auto-save macro on every 2minutes. It was working ok for couple of days, but now it is behaving odd.

Macro is as follows (in Module1):

Public dTime As DateSub MyMacro()
Dim fPath As String


Application.DisplayAlerts = False


dTime = Now + TimeValue("00:02:00")
Application.OnTime dTime, "MyMacro"


fPath = ThisWorkbook.Path


    ThisWorkbook.SaveAs Filename:=fPath & "\" & ThisWorkbook.Name


Application.DisplayAlerts = True


End Sub

Sub AutoDeactivate()


On Error Resume Next
    Application.OnTime dTime, "MyMacro", , False
    
End Sub

This will save the file once it is run... In workbook module, I have:

Private Sub Workbook_Open()

Application.DisplayDocumentInformationPanel = False


Application.Visible = False: UserForm1.Show
    
Application.OnTime Now + TimeValue("00:02:00"), "MyMacro"


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)


Call AutoDeactivate


End Sub

What is happening is that, once this workbook is closed, it seems that the autosave macro is still running in the background, and 2minutes later, it will auto open the workbook (I assume to save it).

One thing which crosses my mind is just to change Call Module1.AutoDeactivate, but I don't see this as possible issue (at least I hope so)... I mean, since autosave macro is running in the background, seems it is not closed properly on close of my workbook...

Any advices?

Thanks in advance
pella88

 

[pella88]

Hi,

Just to add, this issue happens only when another excel is opened when this workbook is closed. 2mins later, it will open this workbook...

Br
pella88

 

[John_w]

The problem is that your Workbook_Open calls OnTime without saving the time in the dTime variable, so if you close the workbook within 2 minutes of opening it the AutoDeactivate routine doesn't cancel the timer because dTime is zero. And if you have another workbook open before closing the macro workbook then the timer will fire, reopening the macro workbook. See http://www.cpearson.com/excel/OnTime.aspx for a detailed explanation of OnTime.

To fix (untested).

Add this to Module1:

Public Sub StartTimer()
    dTime = Now + TimeValue("00:02:00")
    Application.OnTime dTime, "MyMacro"
End Sub

Change MyMacro to:

Sub MyMacro()
    Dim fPath As String

    Application.DisplayAlerts = False

    fPath = ThisWorkbook.Path

    ThisWorkbook.SaveAs Filename:=fPath & "\" & ThisWorkbook.Name

    Application.DisplayAlerts = True

    StartTimer

End Sub

Change Workbook_Open to:

Private Sub Workbook_Open()

Application.DisplayDocumentInformationPanel = False


Application.Visible = False: UserForm1.Show
    
StartTimer

End Sub