Auto update date on save action

[en fuego sauce]

hi all

Im looking to have a date field update based on the last time the file was updated.

Im thinking that the action will have to run when the file is saved, which will then update the date field.

thanks!

 

[AlphaFrog]

Put this in the ThisWorkbook code module.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeSave([color=darkblue]ByVal[/color] SaveAsUI [color=darkblue]As[/color] [color=darkblue]Boolean[/color], Cancel As [color=darkblue]Boolean[/color])
    Sheets("Sheet1").Range("A1").Value = [color=darkblue]Now[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

It will automatically put the current Date and Time in cell A1 on Sheet1 when you save the file. Change the cell reference to suit.

 

[Walky]

Hi,

Another option, if you want the date & time at which the update took place (not only on saving the workbook), you can place the code below directly in the Worksheet by doing a left-click on the tab, then choosing "View code":

Private Sub Worksheet_Calculate()
Range("A1") = Now()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1") = Now()
End Sub

Rgds,

W.

 

[en fuego sauce]

hi,

another option, if you want the date & time at which the update took place (not only on saving the workbook), you can place the code below directly in the worksheet by doing a left-click on the tab, then choosing "view code":

private sub worksheet_calculate()
range("a1") = now()
end sub

private sub worksheet_change(byval target as range)
range("a1") = now()
end sub

rgds,

w.

When I use this option i get hit with the below error when the macro runs:

"Run-time error '28':

Out of stack space"

 

[Excelaron]

This is what I used in one of my projects

Range("A1").FormulaR1C1 = Now()

 

[AlphaFrog]

When I use this option i get hit with the below error when the macro runs:

"Run-time error '28':

Out of stack space"

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Calculate()
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.EnableEvents = [color=darkblue]False[/color]
    Range("A1") = Now
    Application.EnableEvents = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.EnableEvents = [color=darkblue]False[/color]
    Range("A1") = Now
    Application.EnableEvents = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[en fuego sauce]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Calculate()
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.EnableEvents = [color=darkblue]False[/color]
    Range("A1") = Now
    Application.EnableEvents = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.EnableEvents = [color=darkblue]False[/color]
    Range("A1") = Now
    Application.EnableEvents = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

oh yea! working now!

one last question... how do i insert the words, "last update" into the code?

 

[AlphaFrog]

You're welcome.

oh yea! working now!

one last question... how do i insert the words, "last update" into the code?

Too vague. I don't know what that means.

 

[en fuego sauce]

You're welcome.



Too vague. I don't know what that means.

so that the cell read "Last Update--DATE" not just "DATE" looking to give the cell some kind of label so people know what the Date means

 

[AlphaFrog]

Range("A1") = "Last Update: " & Format(Now, "mmm d, yyyy")

Change the date format to suit.