Comment Line Anchor Point

[quarencia]

I received a spreadsheet where all the comments had a God awful high-contract day glow color format. No problem. Wrote a quick macro to reset them to the default values. But I noticed some strange behavior that I can't explain or fix. When you set the msoShapeType of a comment, it changes the anchor point for the line pointing from the comment box to the cell. Instead of being anchored at the top left, the line is now anchored in the center of the comment box.

The code below will illustrate this behavior.

Sub CommentLineBehavior()

    ' Create a comment in a new blank worksheet.
    ' Edit comment and move it around the spreadsheet with mouse.
    ' The anchor point for the pointer line is at the top left corner.
    ' Run this macro
    ' Now edit and move comment and the anchor point is in the center of the comment box.

    Dim cmt As Comment

    For Each cmt In ActiveSheet.Comments
        With cmt
            ' print autoshapetype to immediate window...it will be 1 (msoShapeRectangle)
            Debug.Print .Shape.AutoShapeType
            ' Set the autoshapetype to msoShapeRectangle
            .Shape.AutoShapeType = msoShapeRectangle
        End With
    Next cmt

End Sub

I can not find any property that has changed when the msoShapeType is set. I can not find any property to change the anchor point of this line.

Any ideas what drives this behavior and how to change it?

 

[igold]

Hi,

I created three comments on new worksheet in columns F, I, P.

I then edited each for size, placement, text, font color, and fill color. After I ran your code all anchor points for the lines to the comments were in the top RIGHT corner at the comment indicator of the cell. Which is where they were when I created the comments.

I am running Excel 2010 on Win 10.

 

[quarencia]

Well, that's strange. Shut down over the weekend and just rebooted and did exactly what you did in a new sheet. I still get the behavior I explained in original post. Go figure. I'm using Excel 2010 and Win7 Pro.

 

[igold]

Also before I posted, I poked around and did not see any methods or properties related to what I thought would be the anchor point.

Good LucK!