little triangle
...pat
is-it possible to change de color of the "sign" that you have in a cell with a
remarq ?
When the background is red, this sign is not visible !
Thank you :)
Stratos Malasiotis
I know that this is not exactly what you want, but it is quite... cool!
In a module add
-------------------------------------------------
Sub test_fncCreateCommentIndicator()
fncCreateCommentIndicator vbBlue, "pat"
End Sub
-------------------------------------------------
and in another add
-------------------------------------------------
Option Explicit
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
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)
HTH
Stratos
...pat
--
PGP ID 0xAE2AE170
Le Savoir de l'Homme n'est rien sans le partage et la communication
(PMK)
"Stratos Malasiotis" <ie...@csv.warwick.ac.uk> a écrit dans le message news:
3992C033...@csv.warwick.ac.uk...