none
auto insert date when another cell is not blank RRS feed

  • Question

  • I created a table but, here is the effect I wanted. In the first column, I want a date automatically inserted (for example in A4) when the cell beside it (or in the next column or B4) is filled-up (This case a phone number. ) Also, I want the date uneditable that regardless that the spelling of the name be changed the date never changes. 

    What function I can use? Thanks in advance 

    Tuesday, December 18, 2018 2:15 PM

All replies

  • Right-click the sheet tab.

    Select 'View Code' from the context menu'.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        If Not Intersect(Range("B2:B" & Rows.Count), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            For Each cel In Intersect(Range("B2:B" & Rows.Count), Target)
                If cel.Value = "" Then
                    cel.Offset(0, -1).ClearContents
                ElseIf cel.Offset(0, -1).Value = "" Then
                    cel.Offset(0, -1).Value = Date
                End If
            Next cel
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (.xlsm).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 18, 2018 8:56 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, December 19, 2018 3:24 AM
  • Hi andychavezjr,

    Did Hans's answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, December 25, 2018 6:20 AM