Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-472048%22%20slang%3D%22en-US%22%3ENeed%20a%20VBA%20to%20enter%20date%20in%20a%20cell%20(F1)%20when%20ever%20cell%20(E1)%20content%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472048%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20inventory%20to%20maintain%20and%20want%20to%20keep%20track%20of%20the%20date%20each%20item%20is%20increased%20or%20decreased.%20All%20the%20total%20inventory%20numbers%20are%20in%20column%20E%20and%20are%20changed%20by%20adjustments%20in%20columns%20B-D%26nbsp%3B%20Example%20if%20the%20inventory%20number%20changes%20in%20row%2011%20I%20need%20the%20date%20to%20change%20in%20F11%26nbsp%3B%20and%20remain%20that%20date%20until%20that%20number%20changes%20again%3C%2FP%3E%3CP%3ERespectfully%20submitted%26nbsp%3B%3C%2FP%3E%3CP%3ERichard%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-472048%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-473318%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20VBA%20to%20enter%20date%20in%20a%20cell%20(F1)%20when%20ever%20cell%20(E1)%20content%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-473318%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20This%20should%20work%20if%20a%20user%20changes%20the%20value%20in%20a%20single%20cell%20in%20range%20%22B%3AD%22%2C%20Needs%20to%20go%20on%20in%20the%20code%20for%20the%20appropriate%20sheet%20as%20opposed%20to%20a%20module.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EOn%20Error%20GoTo%20ErrorHandler%3A%3CBR%20%2F%3EIf%20Not%20Intersect(Target%2C%20Range(%22B%3AD%22))%20Is%20Nothing%20Then%3CBR%20%2F%3EDim%20OldValue%20As%20Variant%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EApplication.Undo%3CBR%20%2F%3EOldValue%20%3D%20Target.Value%3CBR%20%2F%3EApplication.Undo%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EIf%20OldValue%20%26lt%3B%26gt%3B%20Target.Value%20Then%3CBR%20%2F%3ECells(Target.Row%2C%206).Value%20%3D%20Date%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EErrorHandler%3A%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20a%20user%20needs%20to%20be%20able%20to%20change%20multiple%20cells%20at%20once%20(Ie.%20deleting%20values%20from%20a%20selection%3B%20this%20gets%20a%20lot%20messier%2C%20let%20me%20know%20if%20that%20is%20a%20need%20and%20I'll%20try%20to%20figure%20it%20out%2C%20right%20now%20it%20just%20exits%20the%20sub%20and%20does%20nothing%20if%20multiple%20cells%20are%20changed%2C%20not%20sure%20what%20your%20requirement%20for%20having%20multiple%20cells%20changed%20at%20once%20is)%2C%20also%20because%20of%20how%20this%20is%20structured%20using%20the%20undo%20command%20you'd%20have%20to%20hit%20enter%20twice%20after%20entering%20the%20value%20instead%20of%20once%20to%20go%20down%20to%20the%20next%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-475572%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20VBA%20to%20enter%20date%20in%20a%20cell%20(F1)%20when%20ever%20cell%20(E1)%20content%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-475572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F253376%22%20target%3D%22_blank%22%3E%40JWR1138%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20JWR%20for%20response%26nbsp%3B%20I%20am%20very%20new%20at%20this%20as%20you%20can%20see.%20All%20my%20inventory%20totals%20are%20in%20the%20E%20column%20and%20the%20totals%20are%20adjusted%20by%20values%20input%20from%20B%2CC%2C%20and%20D.%20I%20would%20like%20the%20cell%20in%20the%20F%20column%20to%20give%20the%20date%20of%20the%20individual%20row%20when%20ever%20that%20row%20is%20changed.%20I%20am%20still%20unsure%20how%20to%20place%20the%20code%20into%20the%20individual%20cell.%20I%20right%20clicked%20on%20the%20sheet%20tab%20at%20bottom%20and%20clicked%20on%20view%20code%20then%20pasted%20it%20in%20there%20and%20closed%20that%20window%20to%20save%20as%20xlsm%20file.%20I%20am%20missing%20a%20step%20or%20two%20I%20am%20sure.%26nbsp%3B%20I%20appreciate%20any%20help%20you%20can%20give%26nbsp%3B%20thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Richard4591
New Contributor

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

2 Replies

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. 

 

@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

 

 

Related Conversations