Apr 07 2018 11:12 AM
Hi,
I want as a part of my headers as showed in the attached example file, a cell to show the latest date when someone has made any modifications inside the workbook.
Is there any formula that can do this?
BR
Niclas
Apr 07 2018 12:45 PM - edited Apr 07 2018 01:07 PM
SolutionHi Niclas,
This question seems easy at first glance!
But it really cannot be achieved in native Excel, it needs VBA power!
I've updated your workbook with a fairly good solution achieved by VBA which is the Excel scripting language.
The solution is this code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Target.Range("C4") = Now()
End Sub
Which is saved in ThisWorkbook code scope.
With this code, each time you save the workbook (which is means that you've made changes to it) this code will trigger and fill the cell C4 with the today's date and time.
Please find the attached file.
Hope that helps
Apr 08 2018 09:17 AM
Apr 08 2018 10:32 AM
Jun 27 2018 09:14 PM
Hi Haytham
This is the exact answer to my query. What would help further is if you could advise how and where to add the string to create the desired result.
Thanks
Linda
Jun 27 2018 10:07 PM
Jun 29 2018 09:12 PM - edited Jun 29 2018 09:13 PM
Hi @Linda Burnside,
If you mean by string the code I mentioned before, this is a VBA code, and you need to save it in ThisWorkbook code module.
Please follow this link to learn how to install this code into your own workbook.
After you install it, don't forget to save your workbook as .xlsm extension because the regular extension .xlsx doesn't allow storing VBA codes.
Regards
Jul 16 2018 09:55 PM
Jul 23 2018 07:13 AM
Haythem,
Running this code in my workbook produces the error "Run-time error '424': object required".
I have the vba code in ThisWorkbook module and have changed the cell as follows:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Target.Range("B17") = Now()
End Sub
How can I get around this?
Thanks
Jul 23 2018 08:23 AM
Hi @bob ....,
Please try this code instead:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("B17") = Now()
End Sub
But don't forget to change the sheet name (Sheet1) into the real name of your existing worksheet.
Regards
Jul 23 2018 08:34 AM
Works perfectly.. thank you.
Apr 07 2018 12:45 PM - edited Apr 07 2018 01:07 PM
SolutionHi Niclas,
This question seems easy at first glance!
But it really cannot be achieved in native Excel, it needs VBA power!
I've updated your workbook with a fairly good solution achieved by VBA which is the Excel scripting language.
The solution is this code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Target.Range("C4") = Now()
End Sub
Which is saved in ThisWorkbook code scope.
With this code, each time you save the workbook (which is means that you've made changes to it) this code will trigger and fill the cell C4 with the today's date and time.
Please find the attached file.
Hope that helps