Forum Discussion

Niclas Johansson's avatar
Niclas Johansson
Brass Contributor
Apr 07, 2018

Formula that's automatic adding date when specific workbook is modified

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

  • Hi 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

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi 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

    • Niclas Johansson's avatar
      Niclas Johansson
      Brass Contributor
      Hi,
      Thanks a lot for the help with this issue.
      The solution you provided works perfectly fine.
      I`m always fascinated with how knowledgeable people are able to find solutions so quick. Even if they don`t know how to do it from the beginning.

      I guess thats the main difference between a beginner and more advanced users.

      Thats the first time I have been seeing VBA within excel... interesting

      I will try learning as I progress within excel. For sure it`s a quite big and complex application with tons of features to explore.

      Thanks again. I Will for sure continue to ask my way forward within this forum when I get stuck.
      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Hi Niclas,

        It is about experience.
        Experts are able to find solutions at a fantastic speed!
        But in fact they have spent a lot of time and effort to reach this level.
        You can become like them!

        Regards
    • Linda Burnside's avatar
      Linda Burnside
      Copper Contributor

      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

      • Linda Burnside's avatar
        Linda Burnside
        Copper Contributor
        I have tried copying the string to my workbook but it doesn't work. Do I need a formula in the destination cell as well?

Resources