Draw Numbered Rectanges over the Comment Indicators...MS Excel 200

[Karen]

I downloaded the CommentsNumbersPrint file in Excel Contextures to draw
number rectangles over the comment indicators on MS Excel 2000 & it worked
fine. However, I now have 2007 version of Excel & when I try to run the
macro it doesn't include the numbers. It does create the rectangle, however,
but provides no numbers as it did in 2000 (within the rectangles).

The following code is the code to draw a numbered rectangle AutoShape over
each comment indicator on the active sheet:

Sub CoverCommentIndicator()
Dim ws As Worksheet
Dim cmt As Comment
Dim lCmt As Long
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

Set ws = ActiveSheet
shpW = 8
shpH = 6
lCmt = 1

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRectangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
With .Fill
.ForeColor.SchemeColor = 9 'white
.Visible = msoTrue
.Solid
End With
With .Line
.Visible = msoTrue
.ForeColor.SchemeColor = 64 'automatic
.Weight = 0.25
End With
With .TextFrame
.Characters.Text = lCmt
.Characters.Font.Size = 4
.MarginLeft = 0#
.MarginRight = 0#
.MarginTop = 0#
.MarginBottom = 0#
.HorizontalAlignment = xlCenter
End With
.Top = .Top + 0.001
End With
lCmt = lCmt + 1
Next cmt

Why would the macro create the rectangle but not put the numbers on the
comments as it did in 2007 version? All advise is prematurely appreciated.

 

[Bob Umlas, Excel MVP]

It seems that you need to do one more step in 2007. Manually, it would be by
selecting the rectangle, using Drawing Tools contextual Format Tab, and in
the WordArt Styles, click the Text Fill and apply black or automatic. then it
shows. Unfortunately, recording this produces nothing! I don't know the code
to simulate the manual steps. FWIW.

 

[Karen]

Thanks, your process works, however, I would have to number the comments
manually and run the risk that this numbering may not match up with the
other macro that produces the comments on a separate sheet - assigning the
numbers consecutively.

Here's that macro that does that on another sheet:

Sub showcomments()
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim cmt As Comment
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

Set newwks = Worksheets.Add

newwks.Range("A11").Value = _
Array("Number", "Name", "Value", "Comment")

i = 1
For Each cmt In curwks.Comments
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = cmt.Parent.Name.Name
.Cells(i, 3).Value = cmt.Parent.Value
.Cells(i, 4).Value = cmt.Parent.Address
.Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ")
End With
Next cmt

newwks.Cells.WrapText = False
newwks.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

The first macro actually numbered the rectangles automatically in Excel 2000
and the above-mentioned macro listed and put the associated comments on a
separate sheet. This method did it all consecutively & there was no risk.
Why must I do this manually in 2007?

There's a third macro that removes the numbers also:
Sub RemoveIndicatorShapes()

Dim ws As Worksheet
Dim shp As Shape

Set ws = ActiveSheet

For Each shp In ws.Shapes
If Not shp.TopLeftCell.Comment Is Nothing Then
If shp.AutoShapeType = _
msoShapeRectangle Then
shp.Delete
End If
End If
Next shp

End Sub

 

[Karen]

Be advised that Debra Delgleish at Contextures fixed the problem --

She added a line in the With
..TextFrame section, to colour the font:

.Characters.Font.ColorIndex = xlAutomatic

and it works now in Excel 2007 and Excel 2003

The revised code is here:

http://www.contextures.com/xlcomments03.html#Number

THANKS AGAIN, DEBRA!!!