Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comments box
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comments box
Hi,
We have no control of the color of the comment in Excel. -- Thanks, Shane Devenshire "Bryan De-Lara" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comments box
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" wrote in message ... Hi, We have no control of the color of the comment in Excel. -- Thanks, Shane Devenshire "Bryan De-Lara" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comments box
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. -- Thanks, Shane Devenshire "Bryan De-Lara" wrote: 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" wrote in message ... Hi, We have no control of the color of the comment in Excel. -- Thanks, Shane Devenshire "Bryan De-Lara" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" wrote in message ... 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. -- Thanks, Shane Devenshire "Bryan De-Lara" wrote: 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" wrote in message ... Hi, We have no control of the color of the comment in Excel. -- Thanks, Shane Devenshire "Bryan De-Lara" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
.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:=msoShapeRightTria ngle, _ 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: 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" wrote in message ... EggHeadCafe - Software Developer Portal of Choice WCF Workflow Services Using External Data Exchange http://www.eggheadcafe.com/tutorials...vices-usi.aspx |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
.Fill.ForeColor.SchemeColor = 12
Found this one at
http://www.ozgrid.com/forum/showthre...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:=msoShapeRightTria ngle, _ 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/tutorials...vices-usi.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comments | Excel Discussion (Misc queries) | |||
Comments | Excel Discussion (Misc queries) | |||
Comments | Excel Discussion (Misc queries) | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
Comments | Excel Discussion (Misc queries) |