200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello all,
I have created a 2 clickable buttons to which I want to assign a macro.
Each button is colagate with one column.
Every time I click a button I would like it to stamp current time and date of the click.
Every time that I click I want it to stamp on the next cell in the column.
Example: button 1- click, time stamp (cell b3) and date stamp (cell c3). The next time I click button 1 that the stamps will be on the next cells in the columns (b4 and C4).
Thanks in advance.
The code below does the job you describe. It is demonstrated in the attached workbook.
Sub DateTimeStamp()
' 11 Apr 2019
' modify constants as desired
Const Clm As Long = 2 ' identifies column B
Const StartRow As Long = 2
Dim R As Long
Dim Ref As String
With Sheets("Sheet1") ' change the name to match the tab name
R = Application.WorksheetFunction.Max(.Cells(.Rows.Count, Clm).End(xlUp).Row + 1, StartRow)
With .Cells(R, Clm)
.Value = Now()
.NumberFormat = "dd mmm yyyy" ' set the date format you desire
Ref = .Address
With .Offset(0, 1)
.Formula = "=" & Ref
.NumberFormat = "mm:hh:ss" ' set the time format you desire
End With
End With
End With
End SubPlease take note of the comments in the code which tell you how you can modify the script.
Note that date and time is a single number in Excel, like 43876.63333. It seems too complicated to split this into two cells for display. The code just places a reference to the first cell into the second. You may like to consider using a single cell only and format the number as Date/Time with a format like this: "dd mmm yyyy hh:mm:ss"