Command button with 'paste date' function to its next left cell

Question

I'd like to create a Commandbutton with functionality to paste a date in to the cell to its left - I need to copy this button below in future.

I am trying:

Private Sub CommandButton2_Click()
    Dim Str As String
    Str = Date
    Range(TopLeftCell).Value = Str
End Sub

Answer 1

I would recommend using Form Control instead of ActiveX control and the reason is very simple. When you copy the button across, the link to the macro remains intact., which is also one of your requirements.

And this is the code that you can use for the CommandButton (Form Control)

Sub Button1_Click()
    Dim cellAddr As String
    Dim aCol As Long

    '~~> Get the address of the cell
    cellAddr = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
    '~~> Also get the column number
    aCol = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column

    '~~> This is required if the button is in column 1
    If aCol <> 1 Then _
    ActiveSheet.Range(cellAddr).Offset(, -1).Value = Date
End Sub