How to Display Date & Time Automatically in Excel Using VBA

Home More Excel (VBA) Training Videos

When I open my  workbook how can I automatically set the value in sheet1 cell a2 and cell b2 to the current date and time respectively using VBA?
Macro code to insert data and time automatically in the Excel worksheet:
  • Click on the developer tab
  • Select Visual Basic
  • Double click on 'thisworkbook' on the left-hand side of the Visual Basic editor
  • Select 'workbook' as shown in the video
  • Two lines of code are inserted automatically by the Visual basic editor
  • Write the code between the 2 lines as shown below
Now everytime you open your workbook the data and time will be displayed automatically and you can use the feature, for example, to find out how many days are left till the start of your vacation!
Private Sub Workbook_Open()
Sheets("Sheet1").Range("A2").Value = Date
Sheets("Sheet1").Range("B2").Value = Time
End Sub

Watch the Excel training video:

Watch the video on our youtube channel