Changin the color of the comment indicator and back!

Hi,

Thanks to the postings here, I was referred to an Excel treasure trove
www.contexture.com. I found a workaround for changing the color of the
Comment Indicator from red to anything else. Few Questions remain though:
1. It seems 80 is the highest color-value it takes (Excel 2002, SP3). Above
80, I a get error message. Is that so?
2. Is there a color-table somewhere I could use to see the available colors,
instead of trial and error in guessing the color?
3. The shape around the indicator remains after the comment is deleted. What
is the code to delete the shape afterwards? Something like:
If (comment == deleted) {
Shape.delete;
}

or alike. Many thanks again.

David McRitchie has lots of info about colors:
http://www.mvps.org/dmcritchie/excel/colors.htm

And if you want to delete the righttriangle if the cell doesn't contain a
comment:

Option Explicit
Sub testme()

Dim myShape As Shape
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

For Each myShape In wks.Shapes
If myShape.AutoShapeType = msoShapeRightTriangle Then
If myShape.TopLeftCell.Comment Is Nothing Then
myShape.Delete
End If
End If
Next myShape

End Sub




ai18ma wrote:
>
> Hi,
>
> Thanks to the postings here, I was referred to an Excel treasure trove
> www.contexture.com. I found a workaround for changing the color of the
> Comment Indicator from red to anything else. Few Questions remain though:
> 1. It seems 80 is the highest color-value it takes (Excel 2002, SP3). Above
> 80, I a get error message. Is that so?
> 2. Is there a color-table somewhere I could use to see the available colors,
> instead of trial and error in guessing the color?
> 3. The shape around the indicator remains after the comment is deleted. What
> is the code to delete the shape afterwards? Something like:
> If (comment == deleted) {
> Shape.delete;
> }
>
> or alike. Many thanks again.

--

Dave Peterson

Thank you Dave,

It works fine. One more question, if I may:

How do I get these macros to work for ALL Excel files? That is, the macros
are automatically loaded when excel.exe is invoked. Thanks again.



"Dave Peterson" wrote:

> David McRitchie has lots of info about colors:
> http://www.mvps.org/dmcritchie/excel/colors.htm
>
> And if you want to delete the righttriangle if the cell doesn't contain a
> comment:
>
> Option Explicit
> Sub testme()
>
> Dim myShape As Shape
> Dim wks As Worksheet
>
> Set wks = Worksheets("Sheet1")
>
> For Each myShape In wks.Shapes
> If myShape.AutoShapeType = msoShapeRightTriangle Then
> If myShape.TopLeftCell.Comment Is Nothing Then
> myShape.Delete
> End If
> End If
> Next myShape
>
> End Sub
>
>
>
>
> ai18ma wrote:
> >
> > Hi,
> >
> > Thanks to the postings here, I was referred to an Excel treasure trove
> > www.contexture.com. I found a workaround for changing the color of the
> > Comment Indicator from red to anything else. Few Questions remain though:
> > 1. It seems 80 is the highest color-value it takes (Excel 2002, SP3). Above
> > 80, I a get error message. Is that so?
> > 2. Is there a color-table somewhere I could use to see the available colors,
> > instead of trial and error in guessing the color?
> > 3. The shape around the indicator remains after the comment is deleted. What
> > is the code to delete the shape afterwards? Something like:
> > If (comment == deleted) {
> > Shape.delete;
> > }
> >
> > or alike. Many thanks again.
>
> --
>
> Dave Peterson
>

Lots of people create a workbook named personal.xls in their XLStart folder that
hold this kind of common routine. (Most people hide this personal.xls workbook,
too via window|hide.)

Since the file is in XLStart, it'll open each time excel starts (as a hidden
workbook).

Then you can use alt-F8 (or tools|macro|macros) to run the procedure.

After you get a few of these routines, you'll want to make it easier to run
them.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

ai18ma wrote:
>
> Thank you Dave,
>
> It works fine. One more question, if I may:
>
> How do I get these macros to work for ALL Excel files? That is, the macros
> are automatically loaded when excel.exe is invoked. Thanks again.
>
> "Dave Peterson" wrote:
>
> > David McRitchie has lots of info about colors:
> > http://www.mvps.org/dmcritchie/excel/colors.htm
> >
> > And if you want to delete the righttriangle if the cell doesn't contain a
> > comment:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim myShape As Shape
> > Dim wks As Worksheet
> >
> > Set wks = Worksheets("Sheet1")
> >
> > For Each myShape In wks.Shapes
> > If myShape.AutoShapeType = msoShapeRightTriangle Then
> > If myShape.TopLeftCell.Comment Is Nothing Then
> > myShape.Delete
> > End If
> > End If
> > Next myShape
> >
> > End Sub
> >
> >
> >
> >
> > ai18ma wrote:
> > >
> > > Hi,
> > >
> > > Thanks to the postings here, I was referred to an Excel treasure trove
> > > www.contexture.com. I found a workaround for changing the color of the
> > > Comment Indicator from red to anything else. Few Questions remain though:
> > > 1. It seems 80 is the highest color-value it takes (Excel 2002, SP3). Above
> > > 80, I a get error message. Is that so?
> > > 2. Is there a color-table somewhere I could use to see the available colors,
> > > instead of trial and error in guessing the color?
> > > 3. The shape around the indicator remains after the comment is deleted. What
> > > is the code to delete the shape afterwards? Something like:
> > > If (comment == deleted) {
> > > Shape.delete;
> > > }
> > >
> > > or alike. Many thanks again.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson