+ Reply to Thread
Results 1 to 3 of 3

Enter data into a cell have comment generated with the current date and time

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    Lithuania
    MS-Off Ver
    Excel 2003
    Posts
    3

    Enter data into a cell have comment generated with the current date and time

    In a worksheet i use cells from B3:B22toG3:G22(bcdef...) but i don't fill all the data in one time.
    It is necessary that when i enter the data into a cell, a comment would be generated with the current date and text(text would be nice, but not realy necessary). For example "the customer was registered: 2011.12.05" the date has to remain static.
    Is there any way to do it? I tried some things with macros, but i'm starting to give up.

    thank you in advance, and sorry for my english.
    Attached Images Attached Images
    Last edited by soulfriend; 12-06-2011 at 05:25 PM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Enter data into a cell have comment generated with the current date and time

    that's a builtin facility in Excel: tracking changes.



  3. #3
    Registered User
    Join Date
    12-05-2011
    Location
    Lithuania
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Enter data into a cell have comment generated with the current date and time

    I found the code that does some of the job,
    Sub CommentDateTimeAdd()


    Dim strDate As String
    Dim cmt As Comment

    strDate = "dd-mmm-yy hh:mm:ss"
    Set cmt = ActiveCell.Comment

    If cmt Is Nothing Then
    Set cmt = ActiveCell.AddComment
    cmt.Text Text:=Format(Now, strDate) & Chr(10)
    Else
    cmt.Text Text:=cmt.Text & Chr(10) _
    & Format(Now, strDate) & Chr(10)
    End If

    With cmt.Shape.TextFrame
    .Characters.Font.Bold = False
    End With

    SendKeys "%ie~"

    End Sub

    and

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then _

    Target.Offset(-1, 0).Value = Now

    End Sub

    The idea is to combine thees to into one i gues. The first code writes a comment with the date. But i don't know how to make it write a coment in all the cell's i need in multiple colums and only when i write something into cell. And how to leave the date static. ;/

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1