Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Comment
How do I automatically add a comment that contains the current date, time and
the users name each time data is entered into a cell or modified? I have a range of cells in an application that I want to keep track of by putting the information in comment. Thanks, Emma |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Comment
On Oct 8, 7:42 pm, Emma wrote:
How do I automatically add a comment that contains the current date, time and the user's name each time data is entered into a cell or modified? I have a range of cells in an application that I want to keep track of by putting the information in comment. Thanks, Emma Hello Emma, This macro will record the who made the change (logged on user name), the time, the old value, and the new value when the cell's contents change. Because this macro uses the Worksheet Change event, you must copy this macro into each sheet's Change event you want to have this capability. 'Writtem April 30, 2007 'Author: Leith Ross 'Summary: Place this code in the Worksheet_Change() event procedure ' Every time a cell's value is changed the date, time , old value, ' new value, and the user are recorded in a comment. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewText As String Dim NewVal As Variant Dim OldText As String Dim OldVal As Variant Application.EnableEvents = False NewVal = Target.Value Application.Undo OldVal = ActiveCell.Value ActiveCell = NewVal Application.EnableEvents = True NewText = "On " & Now() & " cell changed from " & OldVal _ & " to " & NewVal & " by " & Environ("UserName") If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment End If With ActiveCell.Comment .Shape.TextFrame.AutoSize = True OldText = .Text & vbLf .Text Text:=OldText & NewText End With End Sub Installing the Worksheet_Change() Event Macro 1. Copy the Macro code above using CTRL+C 2. Open Excel and Right Click the Sheet Tab the macro will run on. 3. Click on View Code in the pop up menu 4. Press CTRL+V to Paste the macro code 5. Press CTRL+S to Save the macro code in the workbook 6. Close the VBE and return to excel using ALT+Q Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove the red corner in excel comment and keep comment? | Excel Programming | |||
automatic cell comment | Excel Programming | |||
Create a condition and allows a automatic comment | Excel Worksheet Functions | |||
How can I edit a comment w/o first having to select Show Comment | Excel Discussion (Misc queries) | |||
a comment plugin & copy paste directly from excel to comment ? fr. | Excel Worksheet Functions |