Create a timestamp in excel using VBA that is static

[hufflefry]

very very new to VBA but i have a spreadsheet that has entries by several people as a log.
i have tried the below code but every time the sheet was opened, the dates update:

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd/mm/yyyy")
Else
Timestamp = ""
End If
End Function

After looking through some other queries i tried the below;


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 'Does nothing if several cells are changed

If Not Intersect(Target, Range("A:A")) Is Nothing Then 'Only if cell changed in column A:
Target.Offset(, 1).Value = Timestamp(Target) 'Put the TimeStamp on column B
End If

End Sub


but this doesn't seem to do anything at all.

Can anyone help? I have had circular formulas before but found that it caused issues with lots of people in and out.

Thanks!

 

[Joe4]

Welcome to the Board!

That code MUST be placed in the Sheet module of the sheet you want to apply it to in order to work.
The easiest way to ensure that is to go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste that VBA code in the VB Editor window that pops up.

Then, as long as you have VBA code enabled, whenever you manually make an entry into column A, it will put a hard-coded date value in column B of that same row.
If you already have a date stamp in column B, and you enter a new value in column A of that same row, it will update the date stamp in column B to today.

The dates in column B will NOT automatically change just by opening the file. A change has to be made to column A in order to trigger the code to run, and then it will only update that particular row. If you notice any other odd behavior, you probably have some other VBA code interfering with things.

 

[hufflefry]

Welcome to the Board!

That code MUST be placed in the Sheet module of the sheet you want to apply it to in order to work.
The easiest way to ensure that is to go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste that VBA code in the VB Editor window that pops up.

Then, as long as you have VBA code enabled, whenever you manually make an entry into column A, it will put a hard-coded date value in column B of that same row.
If you already have a date stamp in column B, and you enter a new value in column A of that same row, it will update the date stamp in column B to today.

The dates in column B will NOT automatically change just by opening the file. A change has to be made to column A in order to trigger the code to run, and then it will only update that particular row. If you notice any other odd behavior, you probably have some other VBA code interfering with things.

ah perfect! thank you!! that did the trick.

 

[Joe4]

You are welcome.
Glad I was able to help!