Static timestamp

[harbour1302]

Hi guys,

First time posting here, i am stuck with a timestamp issue i want B10 to update with a timestamp whenever text is entered into B57 and to show empty when B57 is empty or cleared.

I originally used the formula =IF(ISTEXT(B57),NOW(),"")
However the time updates whenever the sheet is reopened or F9 is pressed.

I require the time to be static, i have researched VBAs but cannot find the correct one.

Can anyone help i am using excel 2016.

 

[Joe4]

Welcome to the Board!

That is because the formula always returns the current date/time. You need to use VBA.

Right-click on the sheet tab name at the bottom of your sheet, select View Code, and paste this code in the resulting VBA window:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(0, 0) = "B57" Then
        If Target = "" Then
            Range("B10").ClearContents
        Else
            Range("B10") = Now()
        End If
    End If
        
End Sub

This is automated VBA code that runs whenever cell B57 is updated.

 

[harbour1302]

This is perfect thank you!!

Legend

 

[Joe4]

You are welcome!
Glad I was able to help!

 

[harbour1302]

Hi,

I am now getting an Application-defined or object-defined error.

I believe this is due to the worksheet being protected.

Is there a way to unprotect to allow code to run and reprotect at the end or bypass lock and run code?

Thanks

 

[Joe4]

Just start out unprotecting the sheet and then re-protect it at the end, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(0, 0) = "B57" Then
        ActiveSheet.Unprotect "password"
        If Target = "" Then
            Range("B10").ClearContents
        Else
            Range("B10") = Now()
        End If
        ActiveSheet.Protect "password"
    End If
        
End Sub

Substitute "password" with your password.

 

[harbour1302]

Yes, perfect.

Thanks again you Rock!