Written by Allen Wyatt (last updated December 21, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003
Vinay uses an Excel worksheet for data entry. Information is entered in column A and Vinay would like to have a way to automatically add a time into column B, adjacent to the value entered in column A, that indicates when the value was entered.
There are several different ways you can accomplish this task. The first is to manually enter a time by selecting the adjacent cell in column B and pressing Ctrl+Shift+; (that's the semicolon). This shortcut enters the current time in the cell. The problem with this approach, of course, is that it isn't automatic and it takes some extra movement and keystrokes to implement.
A better approach would be to use a formula to enter the time. The NOW function returns the current date and time, and you can use it in a cell in this manner:
=NOW()
Of course, this simple formula is updated every time the worksheet recalculates. That means that the function returns the current time every time you enter a value in column A. This is undesirable because you don't want previous times to update. You could try to use a formula to check to see if something is in column A, as in this manner:
=IF(A3="","",IF(B3="",NOW(),B3))
The problem is that a formula like this introduces a circular reference into the worksheet, which presents a whole host of challenges to work with. A better approach is to create a macro that automatically runs every time something is entered in column A. Right-click on the tab of the worksheet used for data entry and choose View Code from the Context menu. You'll see the Code window for the worksheet in the Visual Basic Editor, and then enter this into the window:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rCell As Range Dim rChange As Range On Error GoTo ErrHandler Set rChange = Intersect(Target, Range("A:A")) If Not rChange Is Nothing Then Application.EnableEvents = False For Each rCell In rChange If rCell > "" Then With rCell.Offset(0, 1) .Value = Now .NumberFormat = "hh:mm:ss" End With Else rCell.Offset(0, 1).Clear End If Next End If ExitHandler: Set rCell = Nothing Set rChange = Nothing Application.EnableEvents = True Exit Sub ErrHandler: MsgBox Err.Description Resume ExitHandler End Sub
With the macro in place, anytime you enter something into a cell in column A, the adjacent cell in column B will contain the date and time (formatted to show only the time). If you delete something in column A, then the adjacent cell in column B is cleared, as well.
Note, as well, that you can change the setting for the NumberFormat property to reflect how you want the date to appear. As an example, you could change to a twelve-hour time format by changing "hh:mm:ss" to "h:mm:ss AM/PM".
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3116) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Recording a Data Entry Time.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow ...
Discover MoreWhen you type information into a cell, Excel tries to figure out what type of information you are entering. If Excel can ...
Discover MoreWant a quick way to enter a series of single digits into consecutive cells? The best approach is with a macro, and this ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-12-12 21:31:46
sunny
Your support is very useful .
I appeciate Formula for append 5 previous TimeStamps to current TimeStamp whenever cell changes.
2021-12-12 20:28:14
sunny
What about updating the cell and automatically update the time? Thanks for free support!
2021-12-09 07:22:57
stanley
Hi,
I have successfully updated but in time format the date also prefix to that. how i have to remove?
pls help on this
2020-01-09 06:57:17
Vin
So i have realised its not picking it up because my values are chosen as a data validation list.
Is there any way it can pick up the specific text of the list?
2020-01-09 06:18:20
Vin
Hi there,
How do i make this work for only a specific text entry?
Essentially i have a data validation list - with a number of different options that track status
One of these options is "Active", so when the user selects active i want the date to be put into the sheet on the active column. I don't want the active column to populate based on any selection.
Can anyone advise?
Thanks,
Vfox
2019-12-06 08:12:13
Willy Vanhaelen
@Adrian
Since this is an event macro, you cannot run it by yourself. It only runs automatically when you make an entry. So make an entry in column A and see what happens.
2019-12-05 20:18:57
I understand the concept of the Macro, I am now just having trouble how to enter the data. what should i do first, enter the data then run the macro, or the other way around?
2019-07-11 14:10:59
Ludwig Müller
I love your SOLUTIONS - thanks a million times!
And yes I have a question: Cell A1 is updated every 10 minutes and - thanks to ExcelTips - the update date and time is in B1.
How can I automatically copy both cells, A1-B1, to e.g. C1-D1 and have the following every 10 minutes updates automatically copied from A1-B1 to C2-D2, C3-D3....Cn-Dn?
I try to figure this out since a week, no success.
Best regards and Thank You!
Ludwig
2019-06-21 14:02:24
Gary
Awesome tip! I don't know VB, and I need to timestamp 2 columns in a sheet - one for a request, one for when the request is completed.
Suggestions?
2019-05-14 04:46:46
Gopalakrishnan
Gentlemen,
Also looking for a Script for Google Docs, which will hide Menu, Formula and Tool bar for others while sharing.
Thank you !
Cheers
GK
2019-05-13 11:18:55
Gopalakrishnan
Subscribed mate.
I have been scouring the complete ocean of internet, including some of the 'so called hi-attitude technical guys forums', YOURS and just YOURS (your code) is the only ONE that works without a glitch, smooth as a silk.
Thank you thank you thank you.
PS : Mate, I subscribed twice and still did not get the email for confirmation, which I believe you need to seriously look into. No body should be missing your tips IMO. Thank you Sir !
2019-04-26 13:47:10
Willy Vanhaelen
@Ramzi
Copy the macro to ThisWorkbook and change the Sub name to:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If you don't specify anything for the Sh variable it will run on all sheets in the workbook. If you don't want this, you have to specify the sheets allowed as for example:
If Sh Is Sheet1 Or Sh is Sheet3 Then
'your code
End if
2019-04-25 16:42:08
Ramzi
How do I get this code to work for multiple sheets in a workbook without copying the code into every worksheet?
2019-01-22 10:04:46
alex
when you close the excel , it stops and when you restart excel you have to enter the code again
is there any solution?
2018-01-17 11:51:07
Willy Vanhaelen
@KK
Try this macro:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1) > "" Then
.Offset(0, 1) = Now
.Offset(0, 1).NumberFormat = "hh:mm:ss"
Else
.Offset(0, 1).Clear
End If
End With
End Sub
Make sure you save the file as macro enabled (xlsm or xlsb extension).
2018-01-16 22:56:21
KK
I copied the below code to my file it was working all right but now it has stopped working. How to fix it please.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
2017-10-13 06:00:28
Willy Vanhaelen
@Arpit Dhokriya
This little macro will do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 5 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1) > "" Then
.Offset(0, 3) = Now
.Offset(0, 3).NumberFormat = "hh:mm:ss"
Else
.Offset(0, 3).Clear
End If
End With
End Sub
2017-10-12 18:41:31
Arpit Dhokriya
i WANT TO TAKE AN ENTRY IN COLUMN E AND RECORD THE TIME IN COLUMN H. Please help me in fixing it. below is the code am using.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("c:c"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
2017-10-12 18:37:55
Arpit Dhokriya
i WANT TO TAKE AN ENTRY IN COLUMN E AND RECORD THE TIME IN COLUMN H. Please help me in fixing it. below is the code am using.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("c:c"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
2017-06-19 12:13:19
Kishore
Is is the same possible in Ms access
2017-05-01 11:48:06
DILSHAD AHMAD
I HAVE 2 COLOM. FIRST COLOM NAME IS
WHEN I PUT ONLY SUPOSE T OR G AUTOMATIC TIME COME CURRENT IS IT POSSIBLE
A B C
DELL ENERGY TIME
AMER AMIT
Please Answer ASAP
Best regards,
2017-03-29 13:47:45
sam
Oh, That's exactly what I got .. a circular reference :( .
My data ib cell A1 changes continuously ( like Data monitoring )
( e.g. A1 = 5 @2:00, A1 = 7 @ 2:10 etc )
I wanted to tabulate data in cell A1 against time in columns C ( Time stamp ) and D ( Value @ timestamp ) like
C . . . . . . . . D
02:00 . . . . 5
02:10 . . . . 7
Problem is I can get 5 in D1 @02:00 but if time .is not. 02:00 then D1 becomes either FALSE or = current value, depending on formula.
How to keep 5 unchanged in D1?
I have not played with Macro anytime but will work on it if it is needed.
Thanks in advance.
2017-03-17 07:22:39
Willy Vanhaelen
@Samar
On his comment of 4 March 2016 Mahi asked the same question so my anwer of 5 March gives you the answer.
2017-03-16 08:57:17
Samar
Hi Allen,
Thanks for the Tips.
I'd like to know the code which automatically inserts date in Column A for each change / entry in corresponding row of Column B.
I tried changing the codes...but didn't get it done for myself.
Please help.
Thank You.
2017-02-24 10:02:55
Mitch Warrick
Hi,
This code is nearly perfect for me. I just have a few things i need help with...
I want the code to auto-populate the date in a linear number format and then add a tracking/claim number to the end. Can it be formatted like this "02242017-01" with the "-01" increasing by column entry, i.e. "02242017-02"?
Also, I need it to auto-populate in the B column, when text is entered into the J column.
Please Help!
Thanks,
Mitch
2017-02-06 05:37:03
Barry
@Kathleen
Check your regional settings within Windows are set the same on both machines.
2017-02-05 14:27:49
Kathleen
In the old version of Excel we can enter a date as day month year. The new version requires the entry as year, month & day. Is there a way to get excel to accept the data entry the same for all versions?
2017-01-19 11:33:41
The formula:
IF(C3<>"",IF(B3="",NOW(),B3),"") causes a circular reference error unless the formula iterations options are set. However, the options are not saved with the sheet but on the computer, so change with every spreadsheet you open. You need to put in a macro to set the iteration options on opening the workbook:
Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
Application.Iteration = True
Application.MaxIterations = 1
End Sub
That solves the problem and the formula works fine every time.
2017-01-07 16:21:18
Rashmi
Hi Friends,
In a MS Excel work-book sheet, I have a drop down cell. I want to track the previous value and the date when it changes every time in a new row. At the end I need to use it for the time difference between each state change.
Waiting for your valuable response.
Thank You.
2016-11-11 07:07:59
Thomas
Hi, thanks for the vba code. I'm trying to use the same code in Google Spreadsheets. Does anyone how I can transfer/covert this code to the code that Google Spreadsheet uses?
Thanks in advance!
Regards,
Thomas
2016-11-08 21:41:08
Anish
@Willy VanHaelen
Thank you!
2016-11-07 09:18:49
Willy Vanhaelen
@Anish
This little macro should do the job including preserving borders at deletion:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column < 22 Or .Column > 23 Then Exit Sub
If .Cells(1) > "" Then
Cells(.Row, 24) = Now
ElseIf Application.CountA(Range(Cells(.Row, 22), Cells(.Row, 23))) = 0 Then
Cells(.Row, 24).ClearContents
End If
End With
End Sub
Format column X to the date/time format of your liking.
2016-11-06 16:45:38
Anish
@Willy
Sorry
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("V:V,W:W"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Thanks
2016-11-04 12:27:22
Willy Vanhaelen
@Anish
I can only answer your question if I see the macro you actually use.
2016-11-03 22:32:13
Anish
Hello,
Im trying to udpate information in columns V & W.
If i update column V, the timestamp appears in the next column (W)
How can i set this to ONLY produce a timestamp in column X (when im editing columns V & W)
I am also getting an issue where deleting any information typed into columns V&W is removing my cell borders creating a blank looking cell
2016-10-28 11:14:18
Willy Vanhaelen
@Joms
The macro in this tip and the ones of my comments of 30 Sep 2016, 21 Sep 2016 and 10 May 2015 just do that.
2016-10-27 12:07:15
Rob P
Thank you for this.
Is there away to capture data from multiple cells based on when they are updated.
I.e. to produce a table showing values entered each time someone changes the data in a cell and corresponding dates for these changes?
Best wishes,
Rob
2016-10-24 21:52:32
Joms
Hi All,
Would like to ask if its possible when i delete the value on the target Column the time entered on the column where the time comes up will also be deleted?
Please Help.....
2016-10-24 05:54:50
Michael (Micky) Avidan
@emjei,
Try:
----------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 15 And Target.Column <> 12 Then Cells(Target.Row, 15) = Date
End Sub
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
2016-10-24 05:47:50
Michael (Micky) Avidan
@Shana,
Try the following Event-Macro in the Worksheets Module:
------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 3 Then Target = Time
End Sub
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
2016-10-24 00:09:59
emjei
Sorry, I mean EXCLUDING column L.
2016-10-24 00:08:43
emjei
Hi Willy,
I would like to use this macro you posted:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Cells(Target.Row, 1) = Now
End Sub
Here is my case: I want the date to populate in column O for all the updates done in columns A to N but EXCLUDING column O. Is this possible? thank you.
2016-10-21 15:49:43
Shana
Is there a macro for clicking on a cell, only in columns A and B, and it will fill in the time.
2016-10-20 13:07:42
Pic
Please, can you help me to have a code what show the date and time in first cell of row? I try your code, and I read what @Mahi said but I have a file with data validation and Name manager and after I use your code doesn't work this options. Thank you!
2016-10-19 09:08:54
Willy Vanhaelen
@MANU MEHTA
What do you want to appear in each column A, B, C, D and E?
2016-10-19 09:05:13
Willy Vanhaelen
@Janice
This one liner will do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Cells(Target.Row, 1) = Now
End Sub
You must place this macro in every sheet you want to use it in. Right click the sheet tab and select "View Code". That's the place to be.
2016-10-17 15:46:18
Janice
Hi,
I'm trying to add a macro similar to what you described above except for a row instead of a column.
I'm looking for a code that will automatically add the date and time of update to Column A whenever a user adds/edits text in Column B and onwards. I hope to use the same code on 4 sheets within the same workbook (all setup the same way). Can you help me out?
Thanks
2016-10-15 08:09:18
MANU MEHTA
vba to record the time and date to every adjacent columns till column e.
Please help!!
2016-09-30 11:45:50
Willy Vanhaelen
@Niki
This macro should do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 1 Or .Column > 4 Then Exit Sub
If .Cells(1) > "" Then
Cells(.Row, 1) = Now
ElseIf Application.CountA(Range(Cells(.Row, 2), Cells(.Row, 4))) = 0 Then
Cells(.Row, 1).ClearContents
End If
End With
End Sub
Remeber that the macro must reside in the worskheet's code page: right click the sheet tab and select 'View Code'. That's the place to be.
Format column A to the date/time format of your liking.
2016-09-29 12:30:16
Niki
Hi,
Thank you for creating this macro. I am looking to have a time stamp entered in cell A when data is entered in either b, c or d.
If anyone could help me I would greatly appreciate it.
2016-09-22 05:48:30
John
*K7:K35 range for the time, not K7:N35. Sorry.
2016-09-22 05:47:12
John
I have a worksheet that I use to plan out the nightly operations of my work area. The trick for me is that the time that I want recorded is not the time when I change the value of the cell, but when I highlight it yellow.
As I said, I have to plan the night out and so that means entering the information in advance and I use color coding to notate when something is done. Is it possible to set up a sub to record the time in a range of cells (K7:N35) when the color for a cell(N7:N35) is changed?
2016-09-21 11:53:34
Willy Vanhaelen
@Josh
This macro should do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1) > "" Then
.Offset(0, 7) = Now
Else
.Offset(0, 7).ClearContents
End If
End With
End Sub
Format the column to show the time stamp in the format you want.
Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.
2016-09-20 20:07:22
Josh
Hi,
Can someone assist?
I want to put the time stamp on column H instead of Column B. How do I do this?
2016-08-08 00:03:30
Arikrishnan
Wow the code works... Thank you for the assistance..
Is it possible to freeze the Captured Time ? Any code for that ?
2016-08-07 06:41:04
@Narissa
The following code will do what I think you want i.e. put the date of the last change cell B3 on the Front page of your workbook. Format the cells to show the date/time in the format that you want.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Or Target.Column = 4 Then
Cells(Target.Row, 5) = Now()
Worksheets("Front Master").Range("B3") = Now() 'change the worksheet name to match the tab name on the ' "Front Master" Sheet.
End If
End Sub
This code should be put on the codepage for the worksheet upon which it operates. [to get to the worksheets' codepage simply right click on the worksheet's tab and select "View Code"].
2016-08-07 06:21:48
Barry
@Arikrishnan
The following code will do what you want. Format the column to show the date/time in the format that you want.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 3 Then
Target.Range("A1").Offset(0, 1) = Now()
End If
End Sub
This code should be put on the codepage for the worksheet upon which it operates. [to get to the worksheets' codepage simply right click on the worksheet's tab and select "View Code"].
2016-08-06 06:19:22
Arikrishnan
Hi,
The code works great for a single use. I'm looking out to enable time stamp in two different columns based on input in 2 different columns. Ex: If data entered in Column A, Column B should get the Time Stamp and when Column D gets input then Column E should get the time. Basically a Start Time and End Time.
Please assist me with the respective Code.
2016-07-05 21:57:23
Narissa
Hi I am trying to put in a code where it records the date (time not important) of when staff update comments fields in two columns. This on the Front Master Sheet. I have managed to get the code to work for each of the individual sheets but it is not working on my master sheet. The columns I need to record changes in are C & D and the date recorded in E. Any ideas???
2016-07-01 13:34:56
Willy Vanhaelen
@Arikrishnan
Simply delete the 2 lines in the code:
Else
rCell.Offset(0, 1).Clear
2016-06-30 05:55:40
Rahul
Really excellent
2016-06-29 22:58:25
Hi,
This program works great. However the requirement is that when the content in A is deleted, the timestamp in cell B gets deleted in turn. But this should not happen. I need the value in B gets locked once value entered in A and should not get change when we modify cell A again.
Please send response to my email.
2016-06-08 05:59:54
cuats
did some tweak on the code
***add date and time designation
.NumberFormat = "ddmmmyy hh:mm:ssAM/PM"
***change "clear" to "clear contents" only to retain my cell border (if you have a cell border)
rCell.Offset(0, 1).ClearContents
2016-05-06 08:05:34
Sonali
Experts please help.
I need the date and time segregated into two different coloumns after the autocapture. Eg: If data is entered in Column A, date to be capture in Column B and Time in column C.
Also, I need to be able to measure the lapsed time between efforts to establish how long it takes my team to process a task on average. Exactly like Mr. John Gemmell 10 Jan 2016, 17:10
Measuring Metrics in the Workplace:
Please Do Suggest.
Thanks and with my best regards.
Sonali
2016-04-06 13:43:01
Willy Vanhaelen
@MIHA
VBA doesn't support a Comment_Change event so you can't cach it.
Here is a list of supported events (Excel 2007):
Worksheet_Activate
Worksheet_BeforeDoubleClick
Worksheet_BeforeRightClick
Worksheet_Calculate
Worksheet_Change
Worksheet_Deactivate
Worksheet_FollowHyperlink
Worksheet_PivotTableUpdate
Worksheet_SelectionChange
2016-04-05 07:20:56
MIHA
experts, please help to figure out
why my similar code for entering date in text of comment did not catch event of change
Private Sub Comment_Change(ByVal Target As Excel.Range)
Dim pComment As Comment
Dim WorkRng As Range
On Error GoTo ErrHandler
Set pComment = Intersect(Target, Range("H:L"))
If Not pComment Is Nothing Then
Application.EnableEvents = False
For Each pComment In WorkRng
If pComment > "" Then
Set WorkRng = Application.ActiveCell
Set pComment = WorkRng.AddComment(Application.UserName & ":" & vbLf & Now & vbLf)
pComment.Shape.TextFrame.Characters(1, VBA.Len(Application.UserName) + 1).Font.Bold = True
Next
End If
ExitHandler:
Set pComment = Nothing
Set WorkRng = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
2016-03-25 09:55:57
Gerry
Can anyone provide additional assistance on how to get this to work for multiple columns?
What I'm trying to do is when someone adds their initials in the cell, it captures the date/time in a hidden cell to the right. But I have multiple columns. Ex. I enter initials in Cell O200, then on the next action I enter initials in cell Q200, and so on for about 6 or 7 actions.
Any help on adjusting this Macro to do that would be great!
This is the one that I grabbed from other comments below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 15 Or Target.Cells.Count > 16 Then Exit Sub
If Target.Offset(0, 1) = "" Then Target.Offset(0, 1) = Now
End Sub
2016-03-24 15:00:59
Brian
@Willy
thanks, that helped and works like a charm.
Good news for me, I figured out the lock/unlock and protect/unprotect. Woo Hoo!
2016-03-23 16:07:03
BJR
Hello, I am struggling with where exactly to insert or edit the code(s) you suggested to a previous post (John Powell, 3 Nov 2014). Instead of an 1 column offset- I would like the datestamp inserted in Column "M" and target row. Can you show full routine for this?? This would be greatly appreciated.
Your previous suggestion:
@John Powell
Try the following codes:
'---------------------
Public TempVal
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 141 Then TempVal = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 140 Then Exit Sub
If Target <> TempVal Then Cells(Target.Row, 141) = Now
End Sub
'------------
2016-03-23 08:21:51
Willy Vanhaelen
@Brian
If Lcase(rCell) = "yes" Then
2016-03-22 15:47:26
Brian
also, for the last one, I was using this to time stamp when a cell was marked "Yes".
If rCell = "Yes" Then
However, this is case sensitive so that an exact match is needed. Is there a way to make it so it will accept any version of "Yes"?
2016-03-22 15:39:41
Brian
This code is great, but I am a hack when it comes to VBA. I'm trying to help another group out and I need this to run in a shared workbook, on a password protected worksheet.
I need the code to:
- Unprotect the worksheet (with password)
- unlock the cell where the time stamp will be placed
- enter the time stamp
- lock the cell again.
- Protect the worksheet again (with Password)
I appreciate any help!
2016-03-08 11:58:47
suraj Gadkar
after entering data in A column the time will reflect in B column. but at the same time I want to copy data in C column and time should reflect in D column...but I want to copy data in c column using a formula, so that data in column a1 and c1 should be same.
C1 data will be stored in another column and that data will be reflected in C column using a formula.
please help...
2016-03-05 13:24:20
Mahi
Perfect....!!!
Damn, i am still surprised, y didnt it work for me when i tried. Not sure where i messed up with the values.
Thank U So Much :)
2016-03-05 11:46:34
Willy Vanhaelen
@Mahi
Here it is:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 2 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1).Offset(0, -1) = "" Then .Offset(0, -1) = Now
End With
End Sub
2016-03-04 20:50:40
Mahi
@ willy : your code works for me but i am trying to make little adjustment to it. Can you please modify the code so that i need to have have data entered in second column and Date & Time need to be captured in the first column.
I tried different combinations but did not seem to be work. (I am not gud at codes)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1).Offset(0, 2) = "" Then .Offset(0, 2) = Now
End With
End Sub
Any help is greatly appreciated.
2016-03-02 22:01:21
Guy
Worked out Intersect(Target, Range("M:M,Q:Q") targets multiple columns and adds the date in at the same off set.
2016-03-02 21:23:27
Guy Kentwell
Macro works well for one column, how to you nest these so you can get a date entered in multiple columns to track entries in multiple columns in the one Tab
2016-03-02 04:49:17
Kim2
These tips have all been really useful - thank you. I have edited the cell ranges so that the VBA code works for a selection of columns within the same worksheet and I have also edited the offset code however I am finding that when the date updated date is removed from my worksheet that the cell formatting (cell shade and border)is also being removed. Does anyone know if it is possible to add code to ensure the cell formatting remains?
I have recorded a new macro with my chosen border and shading for the range of cells that contain the recording data entry and time but I'm not sure if I can add this code to the existing code or whether I need to create a new code which will run alongside.
I hope this makes sense, I'm a bit of a VBA novice :)
2016-02-29 11:32:29
Robert
Thank you very much. After much searching the internet and many trials and errors, this worked perfectly for what I needed.
2016-01-19 07:04:27
Barry
@Kim,
In the line of code: Set rChange = Intersect(Target, Range("D:D, F:F, H:H"))
Note: the inverted commas
2016-01-18 16:05:09
Kim
I'm using the macro shown but need to apply it to several columns on 1 worksheet. I tried just adding the columns as shown and it worked for the next column but not the ones after. for example ("D:D", "F:F","H:H"))
2016-01-10 17:10:16
John Gemmell
Measuring Metrics in the Workplace:
This tip has been exponentially helpful, but this macro command only allows me to format auto-date-stamping in one column, for one action recorded.
To determine the lapsed time in a given task, I need to be able to account for the actions of two separate departments. The London department enters the task. My department processes the task. I need to be able to measure the lapsed time between efforts to establish how long it takes my team to process a task on average.
This formula allows for Excel to auto-populate a date-time-group stamp following the data entry of a task number, assigned by London, in column 'A.'
My team processes the task order, and Oracle assigns each task with a unique identification number. The 'clock stops' when we have received the Oracle identification number.
I would like to understand how to program two consecutive macros, of the same command, but for two separate actions.
Currently, London is covered; however, my team and I must type "Ctrl+;" space "Ctrl+Shift+;" into the adjacent cell to record the date and time we completed our obligation.
Cordially,
John
2015-12-30 11:21:31
Pablo
Hello, thanks for this macro, it is very useful. I want to use it but so that it targets ONE cell only and then puts the time 4 boxes above. Could you please tell me how to specify one box, please?
I assume placing the time 4 boxes above means changing the offset to (-4,0) but please tell me if that's wrong because these macros are totally new to me!
for an example the box I want to target is D11.
Thanks,
Pablo
2015-12-01 10:44:25
John
Thank You!
2015-11-13 12:03:07
Linda
@Willy
You are a life saver! I did figure out how to save it last night, you'd think that those error boxes would give more direction for the technically challenged persons :)
This works perfect now and I THANK YOU for your help!!!!!
2015-11-13 10:07:20
Willy Vanhaelen
@Linda
1) A workbook with the extension .xlsx cannot contain macros. To save your workbook as a macro-enabled file select: Save As. In the dialog, click on the "Save as type:" box and select Macro-Enabled Workbook (*.xlsm)
2) This version of the macro will exclude the first 19 rows:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 Or .Columns.Count > 1 Or .Row < 20 Then Exit Sub
If .Cells(1).Offset(0, 2) = "" Then .Offset(0, 2) = Now
End With
End Sub
2015-11-12 13:18:20
Linda
@Willy
I guess I spoke too soon :( for some reason it won't save. It works perfectly when I enter it but I can't figure out how to save it correctly. I wish I could include a picture here of what it does, but when I click 'save' it says:
"The following features cannot be saved in macro free workbooks:
-VB project
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes."
On the left, below the task bar at the top, it says:
Project - VBAProject
-VBAProject (job 368.xlsx)
-Microsoft Excel Objects
-Sheet1 (Sheet1)
***First problem: I can't figure out how to "choose a macro-enabled file in the File Type List"! I don't know where to look for "file type list" or what to do???
***Second problem: I need the macro you gave me to be for Column C - BUT NOT START UNTIL ROW 20 - can that be done?
I'm sorry to be such a pain, but macros and VBA projects are out of my comfort zone and I'm just lost. Thank you for any help you can give.
2015-11-12 12:15:52
Linda
@Willy
PERFECT!!! Thank you soooooo much this works just the way I need it to. So grateful for you taking the time to answer me - made my job easier for sure! Really appreciate it!
YAY! :)
2015-11-10 11:35:39
Willy Vanhaelen
@Linda
This little macro will do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1).Offset(0, 2) = "" Then .Offset(0, 2) = Now
End With
End Sub
When you enter data in a cell in column A, the macro looks if the corresponding cell in column C is empty. Only if it is, it puts the date there.
Remeber that the macro must reside in the worskheet's code page: right click the sheet tab and select 'View Code'. That's the place to be.
Format column C to the date format of your liking.
2015-11-09 10:43:11
Linda
Hey @Allen Wyatt OR @Willy Vanhaelen can either of you help with my post on Oct. 30 2015??
Or anyone, for that matter! Really need help on this, I just can't figure it out!!
Thanks :)
*yes I've read all of the comments, but I'm still lost on this one :(
2015-11-07 08:47:22
Willy Vanhaelen
@Michele
You should read all the comments :-)
Wayne asked the same question on 9 May 2015 and I proposed a solution on 10 May 2015.
2015-11-06 11:04:32
Michele
This works great... what if I needed it for more than one column, like A with the time in B, C with the time in D, etc.?
2015-10-30 12:20:06
Linda
This is a great site with GREAT info but I can't figure out how to skip a column for the date.
Example: I enter info into column A, and then I enter the price in column B. I need the date I do this to populate into column C.
I do NOT want the date to change if column A is edited.
The date in column C does NOT need to recognize column B info at all.
A B C
Home Depot $25.00 TODAY'S DATE
ANY help would be so appreciated! Please include the whole formula so I can copy/paste (because I won't know how to insert just a portion of the function) :)
2015-10-29 15:11:31
Isabela
GREATT!! Fabulous! after two days digging here is the solution!!! thanks :D
2015-10-09 06:18:12
Chris
This is fantastic but does anyone know if its possible to have a time and date in for instance column A and then a further time and date in column O? I have tried adding the same values but it wont work. Any help or advice is greatly appreciated.
2015-09-15 14:33:57
Steph
Is it possible to add to the original code you posted so we can also see the user's name that updated the information in the cell.
This post is very helpful. Thank you!
2015-08-14 13:55:03
Lisa
This macro is very helpful, I'm new to Macros I need the time stamp in A column and not in B I'm trying several things to reverse it but nothing is working for me. I also need the time stamp of when I start typing in B column and not when I hit enter. Is there a macro command that can do that?
2015-07-15 06:21:34
Willy Vanhaelen
@HunterC
.Offset(0, -1).HorizontalAlignment = xlLeft
2015-07-14 15:30:16
HunterC
I would like to change the alignment to justify left. Can anyone help me with this in the code?
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 3 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1) > "" Then
.Offset(0, -1) = Now
.Offset(0, -1).NumberFormat = "mm/dd/yy hh:mm AM/PM"
Else
.Offset(0, -1).Clear
End If
End With
End Sub
2015-07-13 10:24:43
Akshay sharma
i want the cell A1 to be copied and pasted in the next cell A2....so on every minute...
2015-06-08 08:05:39
Bharath
@Willy
Thanks Willy it worked :)
2015-06-07 11:59:55
Willy Vanhaelen
@Bharath
These macros must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.
2015-06-06 06:44:23
Bharath
For some reason this macro is not working for me :( I'm very much new to macros, please tell me if there's any prior setting to be made... Please help
2015-05-10 06:11:36
Willy Vanhaelen
@ Wayne
This macro should do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 And .Column <> 3 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1) > "" Then
.Offset(0, 1) = Now
.Offset(0, 1).NumberFormat = "dd/mm/yy hh:mm:ss"
Else
.Offset(0, 1).Clear
End If
End With
End Sub
You can adjust "dd/mm/yy hh:mm:ss" to whatever you need.
2015-05-09 22:15:45
Good formula which works as described; however, only the time is showing in the cell and what if I need a second column to use the same function?
i.e. input data into a cell in column a (date and time) displayed in column in adjacent cell in column b. on the same line I then need data entered into column c and the date time this data was entered in column d.
is it possible to do a macro for this or a formula to cover this?
2015-04-29 02:00:16
Vail
Thanks for the help. couldn't figure this one out all day and you made it appear simple. thank you.
2015-04-24 05:48:05
Barry
@Pape
See comments below on setting the formatting of the cell to display date and/or time
@aaditi
When Sharing workbooks to allow multi-user editing, core parts of basic functionality in Excel is effectively restricted.
Any VBA code to access these components will consequently fail and result in errors, as you are experiencing.
The article on the Microsoft Office website gives a list of restricted functions which macros should not use: http://tinyurl.com/lkawaop
2015-04-24 02:11:15
aaditi
hi,
I want to get of current time and date entry for any editing/addition in excel in a shared and protected workbook for multiple sheets. Above macro is working fine in a normal protected sheet. but when it becomes shared the macro gives error. Please help!
regards,
Aaditi
2015-04-21 11:00:28
Pape
How can I have the time display only in cell A when I write in cell B.
Thanks
2015-04-18 05:18:18
Barry
You can create a Worksheet_Change event on the Worksheet where the input is made, and use that to invoke the record updating macro on the other Worksheet.
However, there is a major gotcha in that the formula may be such that the value doesn't actually change but the date will be updated (in a similar manner that editing but not changing a value would as described by Micky Avidan below).
I haven't tested it but the same may be true for dynamic functions like NOW() but I doubt if these would be used in the scenario described.
Another way is to use the ONTIMER function to check for a change, say every 1 second. In this case you'd definitely have to keep the old value for every cell being monitored and could be become very processor intensive if there are lots of cells being monitored.
2015-04-16 12:51:46
Willy Vanhaelen
@Mihkel
The Worksheet_Change event only fires when you enter something in a cell or delete one. It is NOT fired when the value of a formula changes as a result of entering something in another sheet for instance. So in such a case the macro will not work.
2015-04-15 10:44:53
Mihkel
I have the same problem.. Frustrating!
>>
Pradeep Bharadwaj 27 Jun 2014, 09:31
If the Column A value is getting changed per formula, will the above code works?
I tried in the worksheet and if enter the value in Column A manually, the code is working. If the value in Column A is derived from formula, this code is not working. Please help.
2015-03-08 07:41:04
Barry
@obaydul,
Put this code on to the codepage where you want to record the dates for data changes.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then Cells(Target.Row, Target.Column + 1).Value = Now
End Sub
As noted below if the user selects the cell and starts editing it but then abandons the edit so that the cell doesn't change the date will still be updated. If this is an issue then you need to modify the code as suggested by Micky Avidan below.
2015-03-07 05:40:54
Barry
@Dev,
I do not understand your problem.
If you just want to import the data in the format you specify then I would suggest you use the Text-To-Columns feature using an underscore as the delimiter or if it is in a file use the Text Import feature again using the underscore as a delimiter
2015-03-07 05:35:43
Barry
@Adriane
Put the following code on the code page for the worksheet holding your data:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F8:AB3000")) Is Nothing Then Cells(Target.Row, 5).Value = Now
End Sub
2015-03-07 04:06:24
I have excel sheet, in which data arrieved from somewhere in format ie.DD/MM/YY_____DATA.
In data sheet date is change automatically on regular basis and data value also.
can i able to record data automatically.
its urgent plz help me
2015-03-06 15:43:56
Adriane
Thank you for this article, and all of the insightful comments.This code is great. I'm no developer either, although I have played around in VBA a bit. I really like the edits made to Fraser's code, however, how would one set this to populate into a defined area? For example, if anything in f8:ab3000 is modified, populate column E in the corresponding row in which the change was made. I tried editing the code provided by Barry, and unfortunately cannot get it to work. Any help would be immensely appreciated.
2015-03-01 05:42:41
obaydul
This macro is very much helpful but I've to add multiple column like B records the changes in A, E records the changes in D and goes on, it takes only two column. when I adds more than 2 column it shows
"Compile error:
Wrong Number of argument or invalid property assignment"
Any help will be much appreciated
2015-02-28 18:06:52
John
I have a special case: on another program, a switch is toggled and the target cell changes from 0 to 1. Any idea why the code doesn't work?
I've tried changing the target cell from "general" to "number" but no luck. I'm thinking it's something involving the formula.
Also, is there a code to use one target cell and record changes down one single column instead of rewriting.
2015-02-27 05:20:12
Fraser
@Barry
Thanks ever so much, this has helped me enormously.
Fraser
2015-02-26 07:19:32
Barry
@Fraser
An even easier way is to use the function:
Environ("Username")
2015-02-26 06:39:32
Barry
@Fraser
To get the Windows Username you have to use a Windows API call. The function below when called will return the Windows Username.
Declare Function Get_User_Name Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long
Function GetUserName() As String
Dim lpBuff As String * 25
Get_User_Name lpBuff, 25
GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
The "Declare Function" statement needs to be at the top of a code Module.
2015-02-25 21:48:34
DianaH
@author,
Beautiful coding, works like a charm.
At the moment, your code adds a new timestamp to the right of the cell that was modified. Would you by any chance know how to adjust the coding so that any time a cell in column A is modified, then to only have 1 specific cell (for example, cell B46) to update the existing timestamp?
Thank you so much.
2015-02-20 00:44:37
Fraser
@Barry
Thanks your version was way better and worked straight away, just one quick question is how would I add the windows username to this as well, as I have multiple people editing the spreadsheet.
Thanks
Fraser
2015-02-19 05:05:18
Fraser
Thank you Barry,
I will give it a go, the issue here is I have a spreadsheet that is actually quite long and shared, and I need to know when any rows have been updated, ideally I would also like t tag the user but am struggling as it is :-)
Fraser
2015-02-18 08:30:56
Barry
@Fraser
This macro must be resident on the codepage of the worksheet that it is applicable to i.e. Sheet1
You can delete the line Worksheets(Sheet1).activate the syntax is wrong, and it superfluous.
There is also an error on the line "Set myRngToInspect = Range("a4:44")" this should read "Set myRngToInspect = Range("a4:p4")"
The code at first sight seems to be overly repetitive, but it does handle the case where multiple cells are selected across two or more rows and a value is enter using Ctrl+Enter (which enters the value into all the selected cells). If this is not an issue then the code can be simplified to:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3:P5")) Is Nothing Then Cells(Target.Row, 18).Value = Now
End Sub
Again this must be on the codepage of Sheet1.
This still suffers from the issue Micky Avidan mentions below. In that overwriting the cell value to the same value will update the date/time stamp.
2015-02-18 02:19:12
Fraser Larcombe
Hi,
I am trying to insert a date in a cell when anything in the row has been changed, I am not a programmer and have tried a few things on the web but nothing yet has worked.
This was the closest I have got, it did go through the debug ok, but it never seems to do anything and I cant run it from a macro as none show on the pull down list, I am sure its something simple but haven't been able to work out what it is, can you help?
Option Explicit
Public Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range
Worksheets(Sheet1).Activate
Set myRngToInspect = Range("a3:p3")
If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line4
End If
Application.EnableEvents = False
Range("r3").Value = Now
Application.EnableEvents = True
Line4:
Set myRngToInspect = Range("a4:44")
If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line5
End If
Application.EnableEvents = False
Range("r4").Value = Now
Application.EnableEvents = True
Line5:
Set myRngToInspect = Range("a5:p5")
If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Range("r5").Value = Now
Application.EnableEvents = True
End Sub
2015-01-30 17:15:18
Franco Medina
how can it set it so that when Cell A instead of just writing anything you need to write YES and then it will do the time stamp.
2015-01-23 11:39:35
Willy Vanhaelen
@SRS
Just replace
.NumberFormat = "hh:mm:ss"
with
.NumberFormat = "dd/mm/yy hh:mm:ss"
You can adjust "dd/mm/yy hh:mm:ss" to whatever you need.
2015-01-22 11:13:02
SRS
How can I adjust the macro to format to show date adn time?
2015-01-20 03:37:30
neale
Thank you Allen, much appreciated
2015-01-15 02:52:25
George
I have an excel file on location on a Windows 2008 server. If a user modifies something in the excel (enter data, delete data etc), can I know which user made changes in the excel, and which changes?
2015-01-09 04:59:59
Vijay
Hi,
I have formatted the time displaying cell (Cell "B") with blue color. If i delete the data in colum "A", then color will be erased.
Please suggest me any solution for this issue.
2015-01-08 03:16:42
Vijay
Hi,
Its very useful to me. Thank you so much.
2014-11-08 02:42:38
Pan Tom
Hello there,
I'm trying to keep a set of records containing date but i want to create a macro that will copy the data from a sheet to another where i will keep day by day records. All the data entry should be done to the first sheet and will be erased after copying to the second.
Thankx in advance
2014-11-03 11:19:10
Michael (Micky) Avidan
@John Powell
Try the following codes:
'---------------------
Public TempVal
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 141 Then TempVal = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 140 Then Exit Sub
If Target <> TempVal Then Cells(Target.Row, 141) = Now
End Sub
'------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
2014-11-02 08:41:55
dockhem
Can we do this thing not in other column but in insert comment of the same cell. Or can we put this in watermark over the cell ?
2014-10-03 06:51:52
Dipen Mandalia
This is fantastic work like magic! love it!
2014-09-29 11:31:03
John Powell
Is anyone developing a variant of Micky's formula to monitor several columns and update a single column with a date stamp?
I have 140 columns to monitor and column 141 should be the date stamp if any of them are edited.
If anyone has the modification and is willing to share - that would be great!!
2014-09-23 17:07:48
JohnT
Hi, this macro works well however I am running into the same issue as Aluis above. This macro clears the "Undo" function which is a big issue for me as well.
I wanted to implement this into a shared tracking spreadsheet however if anyone accidentally types over a cell there is no way to Undo.
Thanks.
2014-08-28 09:17:53
Sumit Bansal
Here is another way to enter date/time automatically that would not update on recalculation. It only uses formula and named range (not circular reference)
http://trumpexcel.com/2014/08/date-time-stamp-in-excel-formula/
2014-07-23 08:56:43
Evan
The following Macro worked great for me. When anything is changed in column A, then the date of the change will be displayed in the same row as A but in column O.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Target.Offset(0, 14).Value = Now
End If
End Sub
2014-07-07 14:40:16
aluis
how is this different?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E10:AM2000")) Is Nothing Then
Target(1, 37) = Date
Target(1, 37).EntireColumn.AutoFit
End Sub
2014-07-07 14:35:30
Aluis
Won't this macro clear my undo stack overflow in excel everytime it runs? If so then this is a huge problem for me.
2014-06-28 05:28:22
Michael (Micky) Avidan
Some Corrections+Clarifications:
1) The 'Public TempVal' declaration belongs to the macros.
2 Both Macros should be entered in the Sheets Module.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
2014-06-28 05:25:50
Michael (Micky) Avidan
************************
To whom it may concern.
************************
The proposed Macro is not only too large - it also has a BIG(!) disadvantage because it will change the Date/Time whenever the user, accidentally, presses F2 as to edit a cell, in Column A, and immediately presses Enter (which leaves the cells value unchanged).
The following 2 macros provides the same Date/Time stamp BUT(!) prevents it from being updated in the case mentioned above.Public TempVal
-----------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then TempVal = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target <> TempVal Then Target.Offset(0, 1) = Now
End Sub
---------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
2014-06-27 09:31:46
Pradeep Bharadwaj
If the Column A value is getting changed per formula, will the above code works?
I tried in the worksheet and if enter the value in Column A manually, the code is working. If the value in Column A is derived from formula, this code is not working. Please help.
2014-04-10 12:48:26
in the line of code "Set rChange = Intersect(Target, Range("A:A"))" add the additional columns after A:A eg. Range("A:A,C:C,E:E")
2014-04-09 16:38:32
R
Hi,
This code works great. How would you set this to also work on changes in cells C, E, G, I?
Thanks
R
2014-03-24 04:27:59
Akshay Kulkarni
This tip is wonderful and saves me a painful exercise of auditing the entries inputed by other people.
Many thanks.
Akshay
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments