Help - create macro


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 Sub

Please 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"


Answer the Question

You must create an account to use the forum. Create an Account or Login