Reposition comment box

[Dick148]

Hi,

I want to have all comment boxes moved so when I edit them they are positioned within 2 to 5 columns to the right. I have tried the move & size action, but there are still some comments that appear off the page when I try to edit. I have hidden columns to the left with column 'A' & row '1' frozen and about 1000 comments, to individually move each comment is unrealistic. Am currently upto column FT and have previously had "cannot shift objects off sheet" have overcome this with macro but now some of my messages are all over the place when I edit them.

Hope some one can helpp

Regards

 

[dnickelson]

Here's some code that seems to do what you ask.

Sub moveCommentEditBoxes()
Dim rng As Range

Set rng = Sheets(1).Range("A1:G31")
inc = 50

On Error Resume Next
    For Each cll In rng
        cll.Comment.Shape.Left = cll.Left + cll.Width + inc
    Next cll
On Error GoTo 0


End Sub

Not exactly sure what the deal is with the comment boxes as far as how you position where they are viewed at, but this code does move their edit location.

Need to set your cell range in the 'set rng = ' part of the code. the 'inc' variable lets you set a distance "to the left of the host cells right edge".

 

[Dick148]

Reposition of Comment Boxes

Excellent thanks for your help, it is appreciated.

One other thing, can you add into macro to auto size comment box to text.

Regards

 

[dnickelson]

adding in 'cll.Comment.Shape.TextFrame.AutoSize = True' seems to do the trick.

Sub moveCommentEditBoxes()
Dim rng As Range

Set rng = Sheets(1).Range("A1:G31")
inc = 50

On Error Resume Next
For Each cll In rng
cll.Comment.Shape.Left = cll.Left + cll.Width + inc
cll.Comment.Shape.TextFrame.AutoSize = True
Next cll
On Error GoTo 0


End Sub

 

[Dick148]

Thanks, you have put a smile on my face.

I appreciate your input



Regards

Richard

 

[gaftalik]

Hi dnickelson!

I have almost same problem with my comments which are escaping each time i reopen my sheet i see them sometimes in the end of the cells (60000th row) so i have to bring them back to their place. I have tried to apply your code and modified the range to my range where my comments exist, created a button and assigned that macro to it , none of the codes worked .
I dont know much abt macros but will you guide me please? all i need is to keep them close from their cell

My first post was here : http://www.mrexcel.com/board2/viewtopic.php?p=872407#872407

Thank you much .