• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Function to Capture Date for data entry in a cell

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

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

Else
Timestamp = ""
End If




End Function
 
Last edited by a moderator:
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?
OKApproval date
OKApproval date
OKApproval date
 

Marc L

Excel Ninja
'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 !​
 
@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
 

Attachments

Marc L

Excel Ninja
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 :​
Code:
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 !​
 
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()
 
Top