Moving display of comments closer to parent cell

[achidsey]

Excel Experts,

I've inherited a spreadsheet that has dozens of comments and they're all
located about six columns away from the cell they comment on.

Is there VBA code that would move all my comments to say, 1/2 inch to the
right of the parent cel?

I tried to use the Object Browser to figure this out but couldn't get my
answer. It seems, the Comment Object has a Shape (property? object?) which
has a few memebers(?) which seem promising, such as "Connector" and
"Placement".

Also, isn't there some view in the VBE that lets me see all the properties
of an object? Where I could look at a cell with one of these wayward
comments, and then see the comment, and see the comment's Connector property
or Placement property, and make changes to the values?

Thanks in advance,

Alan

 

[Norman Jones]

Hi Alan,

I've inherited a spreadsheet that has dozens of comments and they're all
located about six columns away from the cell they comment on.

Is there VBA code that would move all my comments to say, 1/2 inch to the
right of the parent cel?

Try:

''===================>>
Public Sub MoveComments()
Dim wb As Workbook
Dim sh As Worksheet
Dim rng As Range
Dim Cmt As Comment

Set wb = ActiveWorkbook '<<======== CHANGE
Set sh = wb.Sheets("Sheet1") '<<======== CHANGE
Set rng = sh.UsedRange '<<======== CHANGE

Application.ScreenUpdating = False

For Each Cmt In sh.Comments
Cmt.Shape.IncrementLeft -100 '<<======= CHANGE
Next

Application.ScreenUpdating = True

End Sub
'<<===================

This macro will move the specified comments to the left. Experiment and
change the IncrementLeft value to suit your needs. A +ve value will move the
comments to the right.

 

[achidsey]

Norman, Thanks for the response. Alan