Inserting date time in Excel using VBA is as simple as writing a formula in a Cell.
The VBA timestamp code is:
This code will show the current date and time in cell A1. And yeah, that is it. it's done.
The “Now” function returns current date and time, and “Format” function formats the output into "mm/dd/yyyy HH:mm:ss" format.
Let's say you have prepared a sheet. In this sheet, you want time to be shown in column B whenever someone does an entry Column A.
Now we need a VBA timestamp code that runs every time when change is made in column A.
The below code gets the work done easily.
To run this code, open VBA and double click on the sheet in the project window on which you want to show timestamp.
Now we have to use VBA event handler since we want to run our code to run every time when a change is made.
From the drop-down above your code, select Worksheet.
Now adjacent to worksheet drop down, you see another drop down. It contains a lot of event handlers. For now, select “change”.
Now copy the above code here.
And it is done. Get back to your sheet and check it.
Private Sub Worksheet_Change(ByVal Target As Range)
This is the fixed and default subroutine name of a change event handler.
If Not Intersect(Target, Range("A:A")) Is Nothing Then
This line checks if the changed cell is in column A or not.
The first line ignores error pop and runs these two lines. If you omit On Error Resume Next you will see error popping up when you delete a value in column A.
If Target.Value = "" Then
:- This line checks if a cell in column A is blank. If yes then
Target.Offset(0, 1) = ""
:- This line deletes adjacent cell value.
And if False
Else Target.Offset(0, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
:- This line enters the current time and date in adjacent of A column.
You may have noticed that there are several event handlers in the drop-down list. Play around them. Try to figure out what each event handler works. And if have any difficulty, write it down in the comments section.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.