Results 1 to 2 of 2
2010-12-08, 14:24 #1
- Join Date
- Aug 2002
- Brooklyn, New York, USA
- Thanked 0 Times in 0 Posts
I have a user who has Excel files that have worksheets that may contain hundreds of comments.each. The user has inherited most of these files and did not create the comments, so we're both learning as we're going along.
The original problem was the usual "Cannot shift objects off the sheet..." message, so I used a macro to modify the properties of each comment. Problem solved.
A second problem was that some of the comments had comment text boxes that were huge, mostly blank spaces. Here I used a macro to autosize each comment and then use an algorithm to resize the comment box if the width were greater than a certain amount. That did not fully resolve the problem but it did clean it up enough to satisfy us both.
The final problem is placement. Most of the comments are near their parent cells, but some are many columns away. I am looking for any ideas on moving a comment closer to its parent cell if it is X amount of distance away, if in fact this can even be done.
Thus it is that I turn to the experts.
Thanks in advance.
2010-12-09, 08:33 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 187 Times in 173 Posts
You'd basically want to loop through the comments and check the difference between its Left and Top properties and those of its Parent cell.Regards,
Microsoft MVP - Excel