Remark in cell
Roy Goverts
The next one has become a brainteaser for the supportdept I work for.
It's about the remark wich can be placed in a cell, in our case in Excel 97
under WinNT.
We're not sure it can be changed, we haven't found the solution to the
following question we received till now:
if you place a remark in a cell in a worksheet it appears as a little red
triangle in the upper right corner of the cell were the remark was placed.
Question: Can the color of this triangle be changed to another one to ie
determine wich dept made the remark. If so, where do I do that.
Roy.
Chip Pearson
You cannot change the color of the comment (remark) indicator.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services
www.cpearson.com ch...@cpearson.com
"Roy Goverts" <rgov...@goverts.demon.nl> wrote in message
news:#OuZv7D6$GA.56@cppssbbsa05...
Stratos Malasiotis
As Chip has pointed, Excel does not provide a way to change the color of
the comment indicators. However, for the specific purpose you want this
feature, the following function may provide a viable alternative.
It creates a similar triangle above every comment in the active
workbook.
The user can specify the colour of this triangle.
In the way I have done it here, I assume that someone is entering
comments in diffrent computers. By default Excel uses the
Application.Username at the comment's first line. Therefore the function
recognises this name and paints only those comments.
You'll have to device a way to pass the colour (it could actually be
hard-coded in the function in a 'select case application.username'
statement).
------------------------------------------------------------------------------------
Option Explicit
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
----------------------------------------------------------------------------
HTH
Stratos