Comment Line Anchor Point

quarencia

New Member
Joined
Apr 12, 2013
Messages
11
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.

Code:
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,540
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Apr 12, 2013
Messages
11
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,540
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,711
Messages
5,637,921
Members
416,993
Latest member
ant8989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top