Recording Time as tooltip

Balan,

Assuming you mean "as a Comment" instead of "as a ToolTip", then the following will do what I think you want. Right click on the sheet tab for your data entry sheet, click on "View Code" and paste this into the code sheet that pops up:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim intColumn As Integer
Dim intRow As Integer
Dim EntryLog As String

For Each cell In Target
intColumn = cell.Column
intRow = cell.Row

If cell.Comment Is Nothing Then
Application.EnableEvents = False
EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss")
cell.AddComment EntryLog
Application.EnableEvents = True
Else
Application.EnableEvents = False
On Error Resume Next
cell.Comment.Delete = True
EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss")
cell.AddComment EntryLog
Application.EnableEvents = True

End If
Next cell

End Sub

See if that works for you.

enjoy

Bariloche, can you also......

Bariloche, I like that code. can you also keep a type of change history in the comment box? i.e what the last text was and who (userid) changed it?
I suppose you could just use the change history function in Excel, but I like the idea of keeping it all in comment boxes..........

If cell.Comment Is Nothing Then Application.EnableEvents = False EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss") cell.AddComment EntryLog Application.EnableEvents = True Else Application.EnableEvents = False On Error Resume Next cell.Comment.Delete = True EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss") cell.AddComment EntryLog Application.EnableEvents = True End If

sure 'nuff, lucky

lucky,

I just knew someone would want that. LOL :-))

Here's the modified code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim intColumn As Integer
Dim intRow As Integer
Dim EntryLog As String
Dim OldComment As String

For Each cell In Target
intColumn = cell.Column
intRow = cell.Row

If cell.Comment Is Nothing Then
Application.EnableEvents = False
EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss")
cell.AddComment Application.UserName & ": " & EntryLog
Application.EnableEvents = True
Else
Application.EnableEvents = False
OldComment = cell.Comment.Text
cell.ClearComments
EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss")
cell.AddComment OldComment & Chr(10) & Application.UserName & ": " & EntryLog
Application.EnableEvents = True

End If
Next cell

End Sub

The only thing I don't like about this is the size of the comment window reverts back to default when I clear the contents and re-enter the info. So, if anyone wants to tinker with the comment shape code so that it can accommodate the text and handle being changed, I won't mind. I just don't have the time right now.

have fun

Bariloche, I like that code. can you also keep a type of change history in the comment box? i.e what the last text was and who (userid) changed it? If cell.Comment Is Nothing Then Application.EnableEvents = False EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss") cell.AddComment EntryLog Application.EnableEvents = True Else Application.EnableEvents = False On Error Resume Next cell.Comment.Delete = True EntryLog = Format(Now(), "m/d/yyyy hh:mm:ss") cell.AddComment EntryLog Application.EnableEvents = True End If

lucky, the answer to yor question

lucky,

Sorry 'bout that. Looks like I only read part of your request. This code should be closer to what you asked for:

Public CellContents As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

CellContents = ActiveCell.Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim intColumn As Integer
Dim intRow As Integer
Dim EntryLog As String
Dim OldComment As String

For Each cell In Target
intColumn = cell.Column
intRow = cell.Row

If cell.Comment Is Nothing Then
If CellContents = "" Then CellContents = "Empty"
Application.EnableEvents = False
cell.AddComment "Changed by: " & Application.UserName & "; Was: " & CellContents
Application.EnableEvents = True
Else
Application.EnableEvents = False
OldComment = cell.Comment.Text
cell.ClearComments
cell.AddComment OldComment & Chr(10) & "Changed by: " & Application.UserName & "; Was: " & CellContents
Application.EnableEvents = True

End If
Next cell

End Sub

Still haven't done much with the comment sizing. Not sure I'll keep after it. Also, this (and the other code of this type) doesn't "record" changes "properly" if the cell selection isn't changed and if the "Delete" key is used to delete the cell's entry. It will record that the cell was changed and what it "was", but if you then enter something in that same cell without selecting another cell first, it won't record that the "was" condition was "Empty". I haven't figured out a work around for this. Unfortunately the Selection event is not fired by the "delete" key action (makes sense though that it wouldn't be).

So, this kinda does what you want, with some limitations. Hopefully its useful.

have fun

PS: I also haven't investigated how much text you can have in a comment before Excel complains about it.