Vba SaveAs at same time if cell changes

Question

I am very new to VBA and I really appreciate any help! I have a code to saveas every day at 00:10hrs that works great, and I have a code for the cell change, but I don't know how to combine them... It needs to saveas if cell changes, but wait to 00:10hrs, subtract 1 day from the saving date and saveas, since temperature data values belong to previous day. Thank you in advance!

'Cell change is at Sheet2
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$20" Then
        Call teste 
    End If
If Target.Address = "$G$20" Then
        Call teste
    End If
End Sub

'ThisWorkbook
Private Sub Workbook_Open()
   Application.OnTime TimeValue("00:10:00"), "Abre"
End Sub

'Module1
Sub Abre()
    Application.OnTime TimeValue("00:10:00"), "Abre"
    Dim datestr As String
    datestr = Format(Now, "yyyymmdd, hhmm")
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "D:\Temperature Data\DailyTemp " & datestr & ".xlsm"
End Sub

'Module2
Sub teste()
MsgBox "Did not work"
End Sub

Egan Wolf · Answer 1 · 2018-01-05T18:32:31.317

According to this post, you can pass arguments to OnTime function. Change your Abre to accept a date, which can be used to save a file and call it with appropriate date from Worksheet_Change and Abre itself. The code below is not tested, but I think you will get the idea. One thing to check is what happens if your Worksheet_Change method get called twice: do you have to cancel previous OnTime before calling a new one?

'Cell change is at Sheet2
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$20" Then
    Call teste
End If
If Target.Address = "$G$20" Then
    Call teste
End If
Dim datestr As String
datestr = Format(Now, "yyyymmdd, hhmm")
Application.OnTime TimeValue("00:10:00"), procedureCalled, , False
procedureCalled = "'Abre """ & datestr & """'"
Application.OnTime TimeValue("00:10:00"), procedureCalled
End Sub

'ThisWorkbook
Private Sub Workbook_Open()
    Dim datestr As String
    datestr = Format(Now, "yyyymmdd, hhmm")
    procedureCalled = "'Abre """ & datestr & """'"
    Application.OnTime TimeValue("00:10:00"), procedureCalled
End Sub

'Module1
Public procedureCalled As String  '<--variable to keep last scheduled procedure, so you can unshedule it in Worksheet_Change
Sub Abre(savedate As String)
    Dim datestr As String
    datestr = Format(Now, "yyyymmdd, hhmm")
    procedureCalled = "'Abre """ & datestr & """'"
    Application.OnTime TimeValue("00:10:00"), procedureCalled
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "D:\Temperature Data\DailyTemp " & savedate & ".xlsm"
End Sub

'Module2
Sub teste()
MsgBox "Did not work"
End Sub

It worked while changing the cell values manually, but automatically it waited for the TimeValue (what I wanted) but ignored the cell changes time, saving with the time I saved the macro changes. Do I have to leave Sheet2 active? I need Sheet1 open 24/7! I've tried to understand what the code does but I cannot yet... I am very new to VBA... I really appreciate your help! — Automat, Jan 02 '18 at 15:53
@Automat I updated my code. I added public variable to keep last scheduled procedure. That way it is possible to cancel it in `Worksheet_Change` and prevent from calling it many times. I don't know if this solves problem you described, but you can give it a try ;) — Egan Wolf, Jan 05 '18 at 18:30

Vba SaveAs at same time if cell changes

1 Answers1