display a date
Hi
when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
Hi,
Right click your sheet tab, view code and paste the code below in. It currently works on data entered in column A so change to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy") Application.EnableEvents = True End If End Sub Mike "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
Hi Richard
If you are new to macros set the Security level to low/medium in (Tools|Macro|Security) before trying out Mike's code. If this post helps click Yes --------------- Jacob Skaria "Mike H" wrote: Hi, Right click your sheet tab, view code and paste the code below in. It currently works on data entered in column A so change to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy") Application.EnableEvents = True End If End Sub Mike "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
Hi,
Since you are only interested to get the date when you enter a number try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Or Not IsNumeric(Target) Then Exit Sub If Not Intersect(Target, [A:A]) Is Nothing Then Target.Offset(, 1) = Date End If End Sub If you are only doing this for a single column, here column A:A, then you don't need to disable events... However if you want this to work for the entire worksheet then you need to include them. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
that works but the date changes every day i need it to stay the date the number was entered thanks "Mike H" wrote: Hi, Right click your sheet tab, view code and paste the code below in. It currently works on data entered in column A so change to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy") Application.EnableEvents = True End If End Sub Mike "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
that works but the date changes every day, i need it to stay the date the
number was entered "Mike H" wrote: Hi, Right click your sheet tab, view code and paste the code below in. It currently works on data entered in column A so change to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy") Application.EnableEvents = True End If End Sub Mike "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
how do i include events?
i have about 20 columns that i want this to work with. i need the date to come up in the cell to the left of the cell that i put the number in thanks "Shane Devenshire" wrote: Hi, Since you are only interested to get the date when you enter a number try this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Or Not IsNumeric(Target) Then Exit Sub If Not Intersect(Target, [A:A]) Is Nothing Then Target.Offset(, 1) = Date End If End Sub If you are only doing this for a single column, here column A:A, then you don't need to disable events... However if you want this to work for the entire worksheet then you need to include them. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
display a date
Hi Richard
Try the below...modified to suit your requirement Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False IF Target.Offset(, 1) = "" Then Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy") End If Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Richard" wrote: that works but the date changes every day, i need it to stay the date the number was entered "Mike H" wrote: Hi, Right click your sheet tab, view code and paste the code below in. It currently works on data entered in column A so change to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Offset(, 1) = Format(Now, "dd/mmm/yyyy") Application.EnableEvents = True End If End Sub Mike "Richard" wrote: Hi when i enter a number into a cell i want the cell next to it to display the date the number was entered |
All times are GMT +1. The time now is 07:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com