You may not know that Excel databases are often updated by different users. For this reason it is necessary to keep track of the different changes or modifications that are made to the data in Excel cells. And so, in this way, we can have knowledge of the last update that was made of a record.
In the following tutorial we will explain how to record the date and time modification of the data or cell records in Excel. Very simply, just like we did in the article explaining how to create an index in Excel using shapes. So we ask you not to stop reading this interesting guide, which as always will be very useful.
How to record the date and time of changing data or cell records in Excel
To record the date and time when the data or cell records were changed in Excel, we will use the Worksheet_Change event. With it, we can run a VBA code at any time when a cell in our spreadsheet undergoes a change. And in this way we can record the changes that are made in our database.
How to record the date and time of changing data or cell records in Excel" src="/images/posts/2b8df8a145cded9144c8b72b0df1a945-0.jpg">
Suppose we have a table that shows four columns, in them we have the name in column A, the surname in column B, the telephone in column C. And we will have the latest update in column D, with an X number of cells. In this column D, I want to insert the record of modification date and time in any of the cells.
Regardless of the changed column, column D will automatically show the date and time of the last recorded change. To perform the registration operation of the date and time of changing data or cell records in Excel. It is to go to the VBA editor, create a code or create a macro, for this we go to the top and create a clip in the Developer tab, then in VBA.
Insert VBA code to record date and time
Now, on the left side we are going to position ourselves on the sheet that contains our table, we create a clip on the right and in the options that will appear, we must select or create a clip in View code. Now on the right side we need to enter the following code:
Private Secondary Worksheet_Change (ByVal Target As Range)
It is Target.Column <4 Then
Cells (Objective. Row, 4). Value = Now
End if
End subtitle
When this event has been triggered, we are going to compare the column cell that has been changed, using the Target property. Column.
If the parameters in this column are less than 4, the current time and date will be inserted using the Now function. Notice that both the time and date are entered in column 4 and in the cell that has been changed and this will be achieved with the Target.Row property.
The next step we will take is to check if this code works, for this we will minimize this window. Then we will go to the sheet where our table is located and make some changes in any cell and column. When you do this, the date and time of the change should appear in column D and in the same row where the change originated.
How to record the date and time of changing data or cell records in Excel" src="/images/posts/2b8df8a145cded9144c8b72b0df1a945-1.jpg">
If, on the other hand, you enter a new name in the box, column D of that same row will show the time and date when you are making the entry. And in this simple way, using VBA, we were able to learn a new Excel function. Where it is possible record the date and time when the data or cell records were changed in Excel.
