Auto-Save issue

pella88

Board Regular
Joined
Aug 14, 2013
Messages
67
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):
Code:
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:
Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

pella88

Board Regular
Joined
Aug 14, 2013
Messages
67
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
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:
Code:
Public Sub StartTimer()
    dTime = Now + TimeValue("00:02:00")
    Application.OnTime dTime, "MyMacro"
End Sub
Change MyMacro to:
Code:
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:
Code:
Private Sub Workbook_Open()

Application.DisplayDocumentInformationPanel = False


Application.Visible = False: UserForm1.Show
    
StartTimer

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,765
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top