Automatic Date Time Entry Using Excel VBA

One of our website visitors wanted to automate the entry of date and time in such a manner that when he had entered all his data like ItemID, Description, Quantity and Price the date and time stamp would appear automatically in the relevant Excel worksheet cell. Using the ‘for next’ loop about which we learnt last time we can achieve our goal. Also why we cannot use the date and now functions is explained in detail. The date function would just give us the date. The now function can give us the date and time entry but it keeps on changing with the change in system time and is therefore volatile and not very useful. We have to solve this problem using Excel VBA to get a non-volatile date and time entry.

Click on the developer tab
Select Visual Basic from the code group
In the Visual Basic Editor double-click on sheet1 on the left-hand side
You could also have landed in the Visual Basic Editor by right-clicking on the Sheet and selecting View Code
Under General on the right-hand side click on the drop-down arrow and select worksheet
In the neighboring text box ‘Selection Change’ appears automatically and the Visual Basic for Applications editor aalo automatically enters the two lines of code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Now click on the drop-down arrow to select ‘Change’ and you’ll get the following lines of code inserted into your work-space:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Now between these two lines of code we write our code as shown below

Private Sub Worksheet_Change(ByVal Target As Range)
‘Using the keyword Dim for Dimension we declare a variable i of data type integer
Dim i As Integer
‘Next we use a looping process ‘We start the loop from row 2 because our worksheet has headers in row 1
For i = 2 To 100
‘Now we define a condition that only if there is data under the headers ItemID, Description, Quantity and Price then alone enter a date under the Date & Time header
If Cells(i, “A”).Value <> “” And Cells(i, “B”).Value <> “” And Cells(i, “C”).Value <> “” And Cells(i, “D”).Value <> “” And Cells(i, “E”).Value = “” Then
Cells(i, “E”).Value = Date & ” ” & Time
‘Note that this format also sohws the seconds in the time
Cells(i, “E”).NumberFormat = “m/d/yyyy h:mm AM/PM”
End If
Next i
Range(“E:E”).EntireColumn.AutoFit
End Sub

Watch the video:


3 thoughts on “Automatic Date Time Entry Using Excel VBA

  1. Gabriela

    How would you write the formula if you wanted to get a time stamp in column E any time any of columns A-D were edited? I don’t necessarily care that all the columns are filled out… I just need to know when it has been edited.

    Reply
  2. karthik

    Hi Sir, I would like to print all the dates for a particular month for my employee salary payslip. so if i type it out a particular month and year then it’s corresponding date should be applied to my employee salary Date fields. so total of 50+ Employees are there in my company. how can i automate it. Thanks in advance.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *