Forum Discussion

Anonymous's avatar
Anonymous
Apr 18, 2019

Need a VBA to enter date in a cell (F1) when ever cell (E1) content is changed

I have an inventory to maintain and want to keep track of the date each item is increased or decreased. All the total inventory numbers are in column E and are changed by adjustments in columns B-D  Example if the inventory number changes in row 11 I need the date to change in F11  and remain that date until that number changes again

Respectfully submitted 

Richard

Formulas and Functions
Macros and VBA

2 Replies

  • JWR1138's avatar
    JWR1138
    Iron Contributor
    Apr 18, 2019

    Hi, This should work if a user changes the value in a single cell in range "B:D", Needs to go on in the code for the appropriate sheet as opposed to a module. 

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler:
    If Not Intersect(Target, Range("B:D")) Is Nothing Then
    Dim OldValue As Variant
    Application.EnableEvents = False
    Application.Undo
    OldValue = Target.Value
    Application.Undo
    Application.EnableEvents = True
    If OldValue <> Target.Value Then
    Cells(Target.Row, 6).Value = Date
    End If
    End If
    ErrorHandler:
    End Sub

     

    If a user needs to be able to change multiple cells at once (Ie. deleting values from a selection; this gets a lot messier, let me know if that is a need and I'll try to figure it out, right now it just exits the sub and does nothing if multiple cells are changed, not sure what your requirement for having multiple cells changed at once is), also because of how this is structured using the undo command you'd have to hit enter twice after entering the value instead of once to go down to the next cell. 

     

    • Anonymous's avatar
      Anonymous
      Apr 19, 2019

      JWR1138 

      Thank you JWR for response  I am very new at this as you can see. All my inventory totals are in the E column and the totals are adjusted by values input from B,C, and D. I would like the cell in the F column to give the date of the individual row when ever that row is changed. I am still unsure how to place the code into the individual cell. I right clicked on the sheet tab at bottom and clicked on view code then pasted it in there and closed that window to save as xlsm file. I am missing a step or two I am sure.  I appreciate any help you can give  thank you

       

       

Resources