Hi Kal,
Here's a solution using a UDF (user-defined function). It solves an issue I
saw with the other proposals that they will generate a new date any time a
cell is edited, even if not changed, or even during any worksheet
calculation. It also works when multiple serial numbers or document IDs are
entered or changed in one operation (copy/paste, fill) unlike the
Worksheet_Change event proposed (although that could be overcome).
Personally, I would usually prefer a Worksheet_Change event, since formulas
aren't ideally suited to retaining permanent values, and depending on how
written, Worksheet_Change could allow for manually changing/correcting the
date (a formula couldn't do that unless the override were entered in another
cell). Worksheet_Change could also only calculate a new date if the date
cell is empty, offering maximum protection against accidental recalculation.
On the other hand, the code would probably need to be maintained later if
the worksheet layout changes, and not everyone is comfortable with that.
Here's my UDF version that stores the last serial number/key value and date
in a hidden cell property called ID, allowing it to only calculate a new
date if the serial number/key value changes:
Function FreezeDate(KeyValue As String) As Variant
Const IDTag = "FreezeDate", FormatString = "yyyy-mm-dd"
Dim lastID As String, newID As String, ParsedID() As String
Dim retDate As Variant
On Error Resume Next
' Return empty string if no KeyValue or bug.
retDate = ""
' No KeyValue, no return date
' -- But the ID is preserved and the last date saved will be returned
' if a matching KeyValue is subsequently provided for this cell
' (useful for copy/paste date preservation)
If KeyValue <> vbNullString Then
With Application.Caller
lastID = .id
ParsedID = Split(lastID, "&")
If UBound(ParsedID) = 2 And ParsedID(0) = IDTag Then
' Seemingly valid ID found, be ready to return last date
retDate = CDate(ParsedID(2))
' Truncate date from lastID for newID/lastID comparison
lastID = ParsedID(0) & "&" & ParsedID(1)
End If
newID = IDTag & "&" & KeyValue
If newID <> lastID Then
retDate = Date
.id = newID & "&" & Format(retDate, FormatString)
End If
End With
End If
FreezeDate = retDate
End Function
To use this UDF, put it in any module (or a new module) in the Visual Basic
Editor, then in the date column, use this formula (as written for row 2):
=FreezeDate(A2)
Replace A2 with a reference to any value that you want the date to be
associated with. If that value changes, the date will be recalculated, but
not for any other reason.
There is a limitation to this UDF: There are certain symbol characters that
the KeyValue can't contain or the UDF will malfunction. That's because not
all characters can be stored in a cell's ID property (the ID property is
designed for HTML export use).
To see how the old date and KeyValue (or serial number) is stored, you can
use this UDF to view the hidden info:
Function id(ref As Range)
Application.Volatile
id = ref.id
End Function
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Kal xcel
Sent: Friday, February 17, 2012 3:00 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Date (value pest) query
Dear Experts,
I am trying to make a document tracker, where I want to put date
automatically. If you see the attachment, when I am giving serial no.
Document no. & date will be appeared automatically. But next day that date
changing to current date which I don't want.
Is there any process to keep the date fixed.
Please help.
--
Kalyan Chattopadhyay
Executive Sales Coordinator
R. S. H. Pvt. Ltd.
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
