Move Excel Cell Comments with VBA

[vb6novice]

Using Excel 2000, my procedure adds a comment to various cells based on their content. When visible, the cells default to a position just above and to the right (of course), which, for my worksheet, is off the screen. I want them to show up to the left of the cell when the mouse is positioned over the cell with the comment. Attempts at moving them with VBA do nothing.

I'm having success with

For Each comment_shape In ActiveSheet.Comments
    comment_shape.Shape.AutoShapeType = msoShapeRoundedRectangle
    comment_shape.Shape.TextFrame.AutoSize = True
Next comment_shape

but when I try to use

    comment_shape.Shape.IncrementLeft (-300)

or

    comment_shape.Flip msoFlipHorizontal

in that same loop neither has any effect, and I don't get an error.

I also get no results and no error from

    comment_shape.Fill.ForeColor.RGB = RGB(128, 0, 0)
    comment_shape.Fill.BackColor.RGB = RGB(170, 170, 170)
    comment_shape.Fill.TwoColorGradient msoGradientHorizontal, 1

Any clue as to why nothing's happening (or why it's not happening)?

 

[rmikesmith]

I had a similar need in an application I developed. What I discovered was that while you can position comment boxes programmatically, if Excel's options are set to comment indicator and the user mouses over the cell, the comment box will be displayed in it's original default position. If you right-click that cell and select show/hide comment to show it, the comment box will display in the programmed position. Therefore, to use this strategy, your application should change the option to Comments: None. You will then need a way to trigger code that displays the comment. It's a somewhat clunky to get it to do what you want, I know.


Regards,
Mike