Apr 18 2019 08:24 AM
Apr 18 2019 08:24 AM
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
Apr 18 2019 01:56 PM
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.
Apr 19 2019 09:56 AM
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