tahnks a lot to all On Sat, Feb 18, 2012 at 12:13 PM, Asa Rossoff <a...@lovetour.info> wrote:
> 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 > -- *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
