Reposition comment box

D

Dick148

New Member
Joined
Nov 20, 2005
Messages
3
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Here's some code that seems to do what you ask.

Code: 
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".
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :cry:

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

Thank you much .
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Macro to pull Office 365 'Comments' onto new tab like I previously did with what's now 'Notes'
Replies
1
Views
160
jkouvel
J
M
  • Solved
Sheet code for input box and comment when cell contains certain value
Replies
7
Views
354
Peter_SSs
Peter_SSs
D
  • Question Question
Linking comment cells to regularly changing info
Replies
3
Views
361
Dan5977
D
M
  • Solved
Stack comments with VBA instead of clear and overwrite
Replies
8
Views
371
MeaclH
M
T
  • Question Question
Adjusting comment positions &
Replies
7
Views
494
rollis13
rollis13

Forum statistics

Threads
1,225,138
Messages
6,183,085
Members
453,146
Latest member
Lacey D

Share this page

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
Back
Top