Auto update date on save action

en fuego sauce

New Member
Joined
Jun 6, 2012
Messages
7
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Put this in the ThisWorkbook code module.

Code:
[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.
 
Last edited:
Upvote 0
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":

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.
 
Last edited:
Upvote 0
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":

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"
 
Upvote 0
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...

Code:
[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]
 
Upvote 0
Try this...

Code:
[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?
 
Upvote 0
Range("A1") = "Last Update: " & Format(Now, "mmm d, yyyy")

Change the date format to suit.
 
Upvote 0

Forum statistics

Threads
1,211,742
Messages
6,103,663
Members
447,876
Latest member
rodqntr

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
Back
Top