1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
| Sub test_fncCreateCommentIndicator()
fncCreateCommentIndicator vbBlue, "nomDeLUtilisateur"
End Sub
Public Function fncCreateCommentIndicator(CommentIndicatorColor As Long, CommentIndicatorName As String) As Boolean
'covers the comment indicators in the activeworkbook with a similar
'triangle of the specified color, based on the Application.UserName property
'Stratos Malasiotis (microsoft.public.excel.programming)
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
'check whether a code name has been entered
If CommentIndicatorName = vbNullString Then GoTo ExitFunction
On Error GoTo ExitFunction
Set aWorkbook = ActiveWorkbook
IDnumber = 0
'loop through all wprksheets in the active workbook and all comments
'in each worksheet and create the comment shapes
For Each aWorksheet In aWorkbook.Worksheets
For Each aShape In aWorksheet.Shapes
If Left(aShape.Name, Len(CommentIndicatorName)) = CommentIndicatorName 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 = CommentIndicatorName & 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
.Placement = xlMove
End With
Return
End Function
' -------------------------------------------------
' As it is now it will colour only the comments that keep the default name
' given by excel (Application.username) |
Partager