Comments box

[Bryan De-Lara]

Can I change the colour of the comments box, i.e. the corner red? I use red
on my absence sheet and the comment box doesn't show unless you hover over
it.
Help would be appreciated.

Bryan

 

[ShaneDevenshire]

Hi,

We have no control of the color of the comment in Excel.

 

[Bryan De-Lara]

Thanks Shane, I thought that might be the case, I have found a vba though
that draws a triangle over all the red triangles, but haven't a clue how to
get it in.

Thanks again.

Bryan.

 

[ShaneDevenshire]

Hi,

I don't know what the code is but if you show it I will tell you where to
put it and how to use it.

 

[Bryan De-Lara]

Ok Shane, here it is,

The following code will draw a triangular AutoShape over each comment
indicator on the active sheet:


Sub CoverCommentIndicator()
'www.contextures.com\xlcomments03.html
Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

Set ws = ActiveSheet
shpW = 6
shpH = 4

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Flip msoFlipVertical
.Flip msoFlipHorizontal
.Fill.ForeColor.SchemeColor = 10 'Red
'12=Blue, 57=Green
.Fill.Visible = msoTrue
.Fill.Solid
.Line.Visible = msoFalse
End With
Next cmt

End Sub
There are quite a few on
http://www.contextures.com/xlcomments03.html#Indicator which may be useful
to others here. Bryan."ShaneDevenshire"

 

[Steven Kemp]

This might help you achieve your goal. Made to switch the side the triangle shows on, but you can substitute
..Fill.ForeColor.RGB = CommentIndicatorColor

Steve in Alaska

from http://www.xtremevbtalk.com/showthread.php?t=307482

Private Function fncCreateCommentIndicator( _
CommentIndicatorColor As Long, _
CommentIndicatorName As String) As Boolean
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
If CommentIndicatorName = vbNullString Then GoTo ExitFunction
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(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



Posted as a reply to:

Re: Comments box

Ok Shane, here it is,

The following code will draw a triangular AutoShape over each comment
indicator on the active sheet:


Sub CoverCommentIndicator()
'www.contextures.com\xlcomments03.html
Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

Set ws = ActiveSheet
shpW = 6
shpH = 4

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Flip msoFlipVertical
.Flip msoFlipHorizontal
.Fill.ForeColor.SchemeColor = 10 'Red
'12=Blue, 57=Green
.Fill.Visible = msoTrue
.Fill.Solid
.Line.Visible = msoFalse
End With
Next cmt

End Sub
There are quite a few on
http://www.contextures.com/xlcomments03.html#Indicator which may be useful
to others here. Bryan."ShaneDevenshire"

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[Steven Kemp]

Found this one at

http://www.ozgrid.com/forum/showthread.php?t=57074&page=3

Steve in Alaska

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

Set ws = ActiveSheet
shpW = 6
shpH = 4

If Target.Count > 1 Then Exit Sub 'Limits the change to 1 cell only
If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub
Target.ClearComments 'Clears any existing comment
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format( Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 'Adds a new comment with the text. CHR(10) is a return.

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Flip msoFlipVertical
.Flip msoFlipHorizontal
.Fill.ForeColor.SchemeColor = 12
.Fill.Visible = msoTrue
.Fill.Solid
.Line.Visible = msoFalse
End With
Next cmt
End Sub




Posted as a reply to:

..Fill.ForeColor.RGB = CommentIndicatorColor

This might help you achieve your goal. Made to switch the side the triangle shows on, but you can substitute
..Fill.ForeColor.RGB = CommentIndicatorColor

Steve in Alaska

from http://www.xtremevbtalk.com/showthread.php?t=307482

Private Function fncCreateCommentIndicator( _
CommentIndicatorColor As Long, _
CommentIndicatorName As String) As Boolean
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
If CommentIndicatorName = vbNullString Then GoTo ExitFunction
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(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

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx