PDA

View Full Version : Sleeper: Managing Comments



MWE
08-16-2005, 11:55 AM
A recent post asked how to either not allow users to enter comments or clearn them automatically; see this thread (http://www.vbaexpress.com/forum/showthread.php?t=4618)

I mentioned a few things that bother me about Excel's comment capability. Perhaps I just do not know how to do things ...

1. is there a way to "lock" a comments size and shape so that it does not move when spreadsheet is closed and subsequently reopened?

2. is there a way to change the color used for the (red) triangle added to the upper right corner of the cell when a comment is added? If for other reasons the fill color of the cell is changed to red, the triangle is no longer visible.

3. is there a way to time stamp a comment? In one applicatin I developed, it was important to know when certain cells were changed and by whom. The users agreed to use comments for this purpose. But although they were "pretty good" about keeping things up to date, there were enough omissions that I ended up writing a background proc that wrote comment data to a hidden spreadsheet along with who and when. This turned out to be pretty useful.

malik641
08-16-2005, 08:54 PM
I don't know the answer to number 1. But I just received a site that had this code in it to changed the color of the triangle in the upper right corner of the cell with the comment (for question 2):


Public Function fncCreateCommentIndicator(CommentIndicatorColor) As Boolean
'covers the comment indicators in the activeworkbook with a similar triangle
'of the specified color, based on the user name
Dim IDnumber As Long
Dim aCell As Range
Dim aComment As Comment
Dim aShape As Shape
Dim aWorksheet As Worksheet
Dim aWorkbook As Workbook
fncCreateCommentIndicator = False
On Error GoTo ExitFunction
Set aWorkbook = ActiveWorkbook
IDnumber = 0
For Each aWorksheet In aWorkbook.Worksheets
For Each aShape In aWorksheet.Shapes
If Left(aShape.Name, Len("CommentIndicator")) = "CommentIndicator" Then
aShape.Delete
End If
Next aShape
For Each aComment In aWorksheet.Comments
Set aCell = aComment.Parent
If InStr(1, aComment.Shape.TextFrame.Characters.Text, ":") > 0 Then
If Left(aComment.Shape.TextFrame.Characters.Text, _
InStr(1, aComment.Shape.TextFrame.Characters.Text, ":") - 1) = _
Application.UserName Then
GoSub CreateCommentIndicator
End If
End If
Next aComment
Next aWorksheet
fncCreateCommentIndicator = True
ExitFunction:
On Error GoTo 0
Set aCell = Nothing
Set aComment = Nothing
Set aShape = Nothing
Set aWorksheet = Nothing
Set aWorkbook = Nothing
Exit Function
CreateCommentIndicator:
Set aShape = aWorksheet.Shapes.AddShape(Type:=msoShapeRightTriangle, _
Left:=aCell.Left + aCell.Width - 5, Top:=aCell.Top, Width:=5, Height:=5)
IDnumber = IDnumber + 1
With aShape
.Name = "CommentIndicator" & CStr(IDnumber)
.IncrementRotation -180#
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.RGB = CommentIndicatorColor
.Line.Visible = msoTrue
.Line.Weight = 1
.Line.Style = msoLineSingle
.Line.DashStyle = msoLineSolid
.Line.ForeColor.RGB = CommentIndicatorColor
End With
Return
End Function

Sub test_fncCreateCommentIndicator()
fncCreateCommentIndicator vbGreen
End Sub

I tested it out and it works fine to turn the triangle green.

As for the third question I'm SURE that there has to be SOME way to automatically put the date in a cell comment when one would be added. I just don't know how, yet. If I have some time I'll see if I can do that (I have a couple macros dealing with cell comments, most of them are some of my first macros).

MWE
08-16-2005, 09:03 PM
I don't know the answer to number 1. But I just received a site that had this code in it to changed the color of the triangle in the upper right corner of the cell with the comment (for question 2):

... snip, snip ...




I tested it out and it works fine to turn the triangle green.

As for the third question I'm SURE that there has to be SOME way to automatically put the date in a cell comment when one would be added. I just don't know how, yet. If I have some time I'll see if I can do that (I have a couple macros dealing with cell comments, most of them are some of my first macros).

this is some pretty interesting code and explains what the "triangle" actually is (seems obvious now). Definately worth exploring.

Thanks

Emily
08-17-2005, 07:31 PM
I don't know the answer to number 1. But I just received a site that had this code in it to changed the color of the triangle in the upper right corner of the cell with the comment (for question 2):
I tested it out and it works fine to turn the triangle green.


The green triangle can be drag away, it's only a triangle drawn over the red triangle.

The shape of green triangle may be altered according to column width.

Justinlabenne
08-17-2005, 10:56 PM
#3 Lots of variations:



Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim szNow As String
szNow = Format(Now, "mm/dd/yy at hh:mm:ss am/pm")
On Error Resume Next
With Target
.ClearComments
.AddComment "Changed value to { " & Target.Value & " } on " & szNow
End With
End Sub

MWE
08-18-2005, 08:47 AM
#3 Lots of variations:



Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim szNow As String
szNow = Format(Now, "mm/dd/yy at hh:mm:ss am/pm")
On Error Resume Next
With Target
.ClearComments
.AddComment "Changed value to { " & Target.Value & " } on " & szNow
End With
End Sub

your approach is interesting and somewhat similar to one approach I have used. My approach was to "reformat" the comment text as:
[user:= username]
[date:= dd-mmm-yyyy; HH:MM]
original comment text

The original text was scanned for "username" and, if found, was removed. Ditto for "[user..." and "[date...]