Changing default Comments settings

Question

When a Comment is inserted in a cell, its default position setting is "Don't move or size with cell".  Is it possible to change this to Move and size with cell so that future comments will default that setting?

There does not seem to be anything about this in Options>Advanced and the only way to change this is to right click each comment and select the correct button on Properties tab.

Answer 1

Hi,

you can set your comment options with VBA:

Sub AddComment()

  With ActiveCell

    .AddComment

    .Comment.Visible = False

    .Comment.Text Text:="Frank Arendt-Theilen:" & Chr(10) & ""

    .Comment.Shape.Placement = xlMoveAndSize

  End With

End Sub

Regards,

Frank

---

If this post answers your question, please mark it for all readers as the Answeror vote if the reply has been helpful.

Answer 2

Hi

Does this mean I would need to call this routine each time I add a comment or would be triggered automatically?

dmkg

Answer 3

Hi,

the first macro selects the comment of the active cell and the second hides the comments:

Sub SelectComment()

  Application.DisplayCommentIndicator = xlCommentAndIndicator

  ActiveCell.Comment.Shape.Select

End Sub

Sub HideComments()

  Application.DisplayCommentIndicator = xlCommentIndicatorOnly

End Sub

Regards,

Frank

---

If this post answers your question, please mark it for all readers as the Answer or vote if the reply has been helpful.

Answer 4

Many thanks for your help Frank.

One more question.  With the routine, the comment box gets inserted but the focus goes back to the cell.  What code do i need to add to get the focus to the comment box?

Answer 5

Hi,

you must call this rountine each time you add a comment. But you have full control how the comment should appear if you expand the VBA statements with additional comment settings.

Regards,

Frank

---

If this post answers your question, please mark it for all readers as the Answer or vote if the reply has been helpful.