Using Add Row and iterative date function

Hi All,

I am having a run time error when running the below script

Sub CommandButton1_Click()

Sheets("Sheet1").Range("A4").Select
ActiveCell.EntireRow.Insert Shift:=xlUp

Sheets("Sheet1").Range("A4:C4").Select
Selection.Borders.Weight = xlThin

Sheets("Sheet1").Range("A4").Select
ActiveCell.Formula = "=IF(B3<>"",IF(A3="",NOW(),A3),"")"

End Sub



I am trying to get a non changing time stamp whenever a row is added using the button "New Sample" in the below excel sheet

sample drop off.JPG

I think the problem is the function =IF(B3<>"",IF(A3="",NOW(),A3),"") as the =now() function works the way I want but the dates/time change each time the document is updated.

I have set up the excel sheet to do enable iterative calculations... do I need to do that to VBA?
Is there a better way to get the results I want than the above method?

Thanks