Macro Function to Capture Date for data entry in a cell

[vikaschandra1993]

Dear All,

need urgent help, suppose I have a column(A) and whenever I enter a non -blank Value in the column(A1, A2 ...), the date must be captured in next column(B1, B2 ...).
Please note that this date must be locked and should not change when I refresh my file tomorrow or day after. For eg: If I made the entry on 1st Aug and do not change the value of the cell, my next column should continue showing 1st Aug whichever date I open the file. I tried below VBA Code but whenever I open the file, the column shows current date not the date at which I input the data. Please help

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd-mm-yy")

Else
Timestamp = ""
End If




End Function

 

[vikaschandra1993]

As an Example: I want to know whenever the line is approved: Whenever user puts OK in a cell, the next cell must thorugh the date at which OK was typed. The problem with above VBA is that everytime i reopen the file on other day, it gives todays date in nect cell using above function

Approval commnet	When is this Approved?
OK	Approval date
OK	Approval date
OK	Approval date

 

[Marc L]

'cause you use a formula … Instead you must use the Worksheet_Change event.​

​

You need further help ? So as per forum rules join at least a sample workbook in order to get a quicker & more targeted answer !​

​

 

[vikaschandra1993]

@Marc Thanks for the hint. Please find attached the workbook
I am looking for capturing the approval date in column E2: E7 whenever OK is placed in D2:D7
I need to capture only the first date when OK was placed and should not refresh everyday
This date must be changed only when I am deleting D2:D7 value

 

[AlanSidman]

change this line of code

Timestamp = Format(Now, "dd-mm-yy")

to

Timestamp = Date

 

[vletm]

vikaschandra1993
Something like this?
You have changed Your ... hopes

 

[Marc L]

I am looking for capturing the approval date in column E2: E7 whenever OK is placed in D2:D7
I need to capture only the first date when OK was placed and should not refresh everyday
This date must be changed only when I am deleting D2:D7 value

According to your attachment paste this code to the Sheet1 worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.CountLarge > 1 Then Exit Sub
        Application.EnableEvents = False
    If Not Intersect([D2:D7], Target) Is Nothing Then
        If UCase(Target.Value2) = "OK" Then Target.Resize(, 2).Value = Array("OK", Date) Else Target(1, 2).ClearContents
    ElseIf Not Intersect([E2:E7], Target) Is Nothing Then
        Select Case Target(1, 0).Value2
               Case "OK": If Not IsDate(Target) Then Application.Undo
               Case Else: Target.ClearContents
        End Select
    End If
        Application.EnableEvents = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[vikaschandra1993]

Thank you everyone for the help. You guys are awesome
I have used Now() function with iterative option kept on using circular reference. Is there any issue with this method? I am using a sharedrive excel for such approval whoch will be open and refreshed multiple times. But i have protected this formula
Formula logic is if date is non zero do nothing else paste NOW()

 

[vletm]

vikaschandra1993
Date or Now ...
If You're asking: Capture Date for data entry in a cell ... then You should use Date.
With Formulas, those values will change daily.