Automatic Date & Time Entry Using Excel VBA

Home More Videos



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.

  • 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
Range("E:E").EntireColumn.AutoFit
End Sub





Watch the video on youtube