I have this worksheet change event, but I think it has disabled the
undo function!
The code below adds a comment to any changed cell, and includes the
Windows User Name of the person changing the cell.
Option Explicit
' This is used by GetUserName() to find the current user's
' name from the API
Private Declare Function Get_User_Name Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long
Function GetUserName() As String
Dim lpBuff As String * 25
Get_User_Name lpBuff, 25
GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment <> "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
GetUserName() & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
'comment perhaps should be resized
End Sub
Bookmarks