none
Run Macro whenever cell changes in EXCEL RRS feed

  • Question

  • Hi

    How to execute a macro when cell value changes and store the changed value in <g class="gr_ gr_38 gr-alert gr_gramm gr_run_anim Grammar multiReplace" data-gr-id="38" id="38">other cell</g> with the date of updates so that we can refer the same in future. The worksheet <g class="gr_ gr_39 gr-alert gr_gramm gr_run_anim Grammar multiReplace" data-gr-id="39" id="39">were</g> updated by many users.

    Please help!

    Thanks & regards

    Fayaz Najeeb


    <o:p></o:p>

    Monday, February 6, 2017 11:28 AM

All replies

  • For example, to store any value entered into cell A2 on another sheet named "Storage" along with the date and time that the value was entered - you could also record the username of the person making the change....

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes to the range as needed

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lngR As Long
        
        If Target.Address <> "$A$2" Then Exit Sub
        If Target.Value = "" Then Exit Sub
        
        'Turn off events to keep out of loops
        Application.EnableEvents = False
        
        With Worksheets("Storage")
            lngR = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Cells(lngR, "A").Value = Now
            .Cells(lngR, "B").Value = Target.Value
        End With
        
        'Turn events back on to get ready for the next change
        Application.EnableEvents = True
    End Sub

    Monday, February 6, 2017 8:15 PM
  • Hi Fayaz,

    Thanks for visiting our forum.

    Then this forum mainly focus on general questions and feedback about Excel. Since your query is more related to macro, I'll move your thread to the following dedicated 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.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, February 7, 2017 2:21 AM
  • Hi Fayaz Najeeb,

    this is another example for the same.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngCell As Range
        For Each rngCell In Target
            addToLog rngCell
        Next rngCell
     End Sub
    
    Sub addToLog(rngCell As Range)
        
    
        With ActiveWorkbook.Sheets("Log")
            Dim intNextRow As Integer
            intNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Range("A" & intNextRow).Value = rngCell.Address
            .Range("B" & intNextRow).Value = rngCell.Value
            .Range("C" & intNextRow).Value = Format(Now(), "mm.dd.yyyy hh:mm:ss AM/PM")
            .Range("D" & intNextRow).Value = Application.UserName
        End With
    End Sub

    Output:

    Regards

    Deepak


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 8, 2017 12:58 AM
    Moderator