View original (French)

AUTOMATIC DATE WITHOUT UPDATE!

norbrodwin Posted messages 14 Status Member -  
via55 Posted messages 14393 Registration date   Status Member Last intervention   -
Hello,

I would like to insert a static date into an Excel cell, automatically, so that it is not updated every time the file is opened.

This date should correspond to the date the file (or copy) was created.

Of course, this date does not correspond to "=today()" :)

Thank you very much for your help in advance.

6 answers

  1.
    Chris 94 Posted messages 1937 Registration date   Status Moderator Last intervention   7 536
     
    Hello,

    This is not at all high tech or geeky as a solution... Personally, I would enter this date and any other invariant parameters "manually" when creating or copying the file.

    --
    People who fight can lose. People who do not fight have already lost.
    (Bertolt BRECHT)
    0
  2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    Open the VBA editor (Alt + F11)
    Select This Workbook in the tree structure and in the blank page copy and paste this code which will record the date in a cell on each save 

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) sheets("Sheet1").Range("A1")= Date 'puts today’s date in A1 of Sheet1 ADAPT cell reference and sheet name End Sub


    Best regards

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  3. norbrodwin Posted messages 14 Status Member
     
    Hello,
    Thank you very much for the response.
    A quick clarification:
    The date I would like to insert automatically is the one when the file is created and (or when the file name is changed during 'Save As') and not the date after each modification of the file (save).

    Thank you in advance for your help.
    0
  4. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    When creating the file, you manually enter the date, and for it to change every Save As, the macro in This Workbook will be: 
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI=True then sheets("Sheet1").Range("A1")= Date 'sets today’s date in A1 of Sheet1 ADAPT cell reference and sheet name End Sub


    Best regards

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  6. norbrodwin Posted messages 14 Status Member
     
    Hello,
    The idea is that the date remains unchanged:

    I have a template file that is used for every new order entry.
    For example, on 01/12 I have an order to enter
    -I open the template
    -I enter my order
    -the date should be implemented automatically (I am faced with systematic forgetfulness of manually entering the date)
    -I do a "save as" (to keep the blank template)
    -on 02/12 I access this same file for the new order from 01/12 (for some reason: consultation, modification ...)
    -the date must not be modified, even if I save while closing the file.

    I hope I was clear with this example :)

    I hope to have a solution to resolve this problem :(
    0
  7. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Indeed, it's not the same issue!

    The procedure should actually be:
    If we open the template file, today's date should automatically be recorded
    When closing the new file, the date should normally remain recorded
    If we open the new file, the date should remain unchanged
    so we need to handle the opening of the workbook based on the workbook's name

    The macro is not BeforeSave but Open (always to be placed in This workbook) 

    Private Sub Workbook_Open() If ThisWorkbook.Name = "Template.xlsm" Then Sheets("Sheet1").Range("A1") = Date End Sub


    Adjust the file name with its extension, the sheet name, and cell reference

    Best regards
    --
    "Imagination is more important than knowledge." A. Einstein
    0