Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Resetting Comment Boxes (2003)

    Hi,

    I think I've seen this before but can't find it.

    I sometimes/often find that comment boxes will get relocated from where they were originally placed (usually near the cell being commented). Not only are they moved, but they seem to be resized. Sometimes the resizing takes an extreme form - the comment box is reduced to 0 height (all I see are the top and bottom borders of the comment box, which allows me to resize it).

    I think the above happens more in sheets where panes have been frozen. When unfreezing panes, the comment boxes remain as they were (moved, resized).

    I saw the following macro:

    Sub MoveComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
    With cmt
    .Shape.Top = .Parent.Top
    .Shape.Left = .Parent.Offset(0, 1).Left
    End With
    Next
    End Sub

    I can understand what it's doing but it seems to have no affect. I tried moving comment boxes away from their cells and then running the macro but nothing changed. I changed the offset parameters but again nothing happened.

    I realize the above macro only handles the issue of comment boxes moved from where they should be. But I'll take a working macro to do that for starters and put it into my Personal.xls.

    So I tried to record my own macro to see what was going on as I moved a comment box. I got

    Sub reposition_comments()
    Range("I21").Select
    Range("I21").Comment.Text Text:= _
    "Text of comment goes here."
    Selection.ShapeRange.Item("Comment 10").Top = 1221#
    Selection.ShapeRange.Item("Comment 10").Height = 55.5
    ActiveWindow.SmallScroll Down:=-4
    Selection.ShapeRange.IncrementLeft 15.75
    Selection.ShapeRange.IncrementTop -300#
    ActiveWindow.SmallScroll Down:=-5
    Selection.ShapeRange.IncrementLeft 7.5
    Selection.ShapeRange.IncrementTop -150#
    End Sub

    Unlike the above macro, I have no clue as to what's going on.

    What I'd like is a macro that moves comment boxes back near where they should be, like the first one above. I can probably change the code so "near" is how I'd like it (eg playing with Offset). It would even be better if the comment box could be resized so that all the text shows by default (if there's too much text, I'll resize it manually but that's rarely an issue) - kind of like double-clicking a row or col border to automatically resize.

    What I'd really like is for MS to fix this problem.

    TIA

    Fred

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Resetting Comment Boxes (2003)

    This should reset the size and position of all the comments on the sheet:

    <pre>Option Explicit
    Sub ResetCommentsSizePosition()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
    With cmt.Shape
    .Top = cmt.Parent.Top + 5
    .Left = cmt.Parent.Offset(0, 1).Left + 5
    .Width = 96
    .Height = 55.5
    End With
    Next
    End Sub</pre>


    Of course you can adjust the numbers to set your own defaults...


    Steve

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Resetting Comment Boxes (2003)

    Hi Steve,

    Thanks for the macro.

    Although different than the first macro I posted, your macro seems to do the same as the one I posted even thought mine does not have the +5 factors. In fact, I reran mine on this machine and it did move the comment box back to where it should be - offset 5 pixels (I guess that's what your +5 is) to the right and above the upper right corner of the commented cell.

    As to size, I guess I didn't make myself clear by using the word "default." I was not looking for the comment box to be put back to its default size, although I'll start with that since the size problem I mentioned is that the comment box will be shrunk to zero height. I was hoping that the comment box would be put back to a size that would allow all of its text to be seen - hence different comment boxes would be different sizes. As mentioned, kind of like double clicking a column border where each column result in being a different size.

    Yes, size does matter.

    Fred

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Resetting Comment Boxes (2003)

    Yes I know that it is similar. The code you posted did not set to the "default" while this does.

    This will "Autosize" it (it is also available if you dbl-click the frame and check "Autosize" in the Alignment tab)

    <pre> With cmt.Shape
    .Top = cmt.Parent.Top + 5
    .Left = cmt.Parent.Offset(0, 1).Left + 5
    .TextFrame.AutoSize = True
    End With</pre>


    Note: You will have to manually add breaks in the line (with Enter) if you want the autosize to be multiple rows...

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Resetting Comment Boxes (2003)

    Hi Steve,

    Thanks for the update.

    I know that the first macro I posted did not set the default size. But as far as moving the comment back to where it "should" be, it still seemed it should work. Unless you're talking about the +5 factor.

    As far as the resizing goes, I didn't know you could format a comment box. Interestingly enough, if you're editing a comment and click the Format menu, you can format the comment but only its font; if you double-click the comment box, you get a lot more properties you can change.

    I see what you mean by having to add line returns in the comment box. Looks like the "automatic size" attribute will string out text without a line enter as far as it has to to keep the text on one line. What would be ideal would be to set a width and let Excel figure out how high the comment box has to be in order to have all the text appear in the box. Anyway, I think that's for another day.

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Resetting Comment Boxes (2003)

    Yes the "+5" is part of the default. The original was lined up at the borders.

    As to setting a width and adjustinig the height to fit, this gets very complicated since it must take into account the font style and size and bold/italic character of each. I don't think there is an easy way, though perhaps someone has seen one.

    You could probably approximate it by getting the width and height of the autosize and taking this area (W*H) and dividing it by the desired New width to get the New Height. You would probably need a factor (1.1 - 1.3) to multiply it by for the extra space needed for each line to wrap at a word...

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Resetting Comment Boxes (2003)

    Hi Steve,

    The original didn't seem to work. Even if I moved the comment box manually before running the macro, nothing seemed to happen. Maybe I'm remembering incorrectly but yours works as advertised.

    As far as the resizing goes, yep, I was thinking kind of along the same lines. So I made a few changes to the macro. I now have:

    <pre>Sub ResetCommentsSizePosition()
    Dim cmt As Comment
    Dim cbox_width As Single, cbox_height As Single
    Dim cbox_desired_width As Single, cbox_desired_height As Single
    cbox_desired_width = 96 'used this as default width
    For Each cmt In ActiveSheet.Comments
    With cmt.Shape
    .LockAspectRatio = msoFalse
    .Top = cmt.Parent.Top + 5
    .Left = cmt.Parent.Offset(0, 1).Left + 5
    .TextFrame.AutoSize = True
    cbox_height = .Height
    cbox_width = .Width
    .TextFrame.AutoSize = False
    'Excel's default comment-box width is 96 pixels
    .Width = cbox_desired_width
    'Excel's default comment-box height is 55.5 pixels
    .Height = Application.WorksheetFunction.RoundUp(((cbox_heigh t * cbox_width * 1.1) / _
    .Width), 1)
    End With
    Next
    End Sub
    </pre>


    The macro works pretty well. If I have a comment with 1 physical line (no line returns and wrapping as needed depending on the comment-box width), the .Height formula works ok. But if I have a comment based on the Excel default comment (my name followed by a line return followed by my text), the .Height formula has problems. If my text is longer than my name, the .Height over-estimates the height needed (it's probably assuming all "paragraphs" [using Word's definition of a paragraph as text separated by a line return] will need the height equal to the longest "paragraph"). On the other hand, if my text is shorter than my name, the .Height under-estimates the height needed. Both of these are as expected.

    I thought about doing some parsing of the text to see how many line returns there are and taking this into account. I could run a loop to create an "intermediate" comment box with each paragraph to see its width and height with AutoSize = True (so the text is on 1 line), use your .Height formula to get the number of lines for that "paragraph" using my desired width, and add the height to a running total. When the loop exits, I'd set the comment text equal to the original text, set AutoSize = False, and set the height to the accumulated height from the loop.

    This definitely did NOT seem worth it since I usually have comments of 1 paragraph - I delete my name, set the font to Regular (not Bold), and type my comment.

    One odd thing I did notice. When I moved the comment out of position to test the macro, the comment box appeared, when I hovered on the cell, in the normal position (ie, offset from the commented cell by 5 pixels to the right and up). But when I went to edit the comment, its position moved to where I had moved it to. I didn't think this was right.

    And one positive thing I discovered: one of the properties of the comment box is its fill. Not only can you set the fill to a color (looks like default is white) but you can set a transparency property. Setting the transparency property to 25% allows you to see the information in the cell underneath the comment box (may work with a lower % but that's all I tried).

    So thanks again.

    Fred

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Resetting Comment Boxes (2003)

    I am not sure of your question (if you have one).

    The problems you note in the sizing you seemed to anticipate (as do I) if you have forced some shorter lines. I am not sure there is an easy way to do this. A "brute force" approach would be to check the font and size of each character and do some "lookup" of what point size (ht and width) each is (I have no idea where you could get the data for this table, it must be availabe since WYSIWYG programs must use it). You would then look for carriage returns or the spaces (and word wraps) to actually fill in the comment to get a good height.

    An alternate approach to let XL do more "heavy lifting" may be to transfer the text to a column whose width is the desired width of the comment box (even this is not straightforward since the "Column Width" (to set the column) is not in points. this can be done via trial -and-error (in code) to set the columnwidth and then check the width, and keep adjusting the columnwidth until the width is what you desire). The transfer must transfer each character looking and placing the font/size, bold, italic, etc in the cell. Once place in the cell, you can autfit the row and then check its height and use this for the comment. This way lets EXCEL do the "lookup" of the point sizs of each character. This is also approximate, though since XL is not completely WYSIWYG.

    About the comment appearance when "Shown/edited", versus displayed when hidden. Your description is what I would expect. The left/top of the comment is for when it is "shown". If the comment is hidden and just "popsup" excel places it where it deems best, typically the default position. When you edit a comment its location is in the position that you set.

    Steve

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Resetting Comment Boxes (2003)

    Steve,

    I didn't have any more questions after my last post. My last post was just a bunch of observations after my experimenting, in case anyone looks up the post and wants to see how it turned out.

    My suggestion on how to calculate the height was intended to be a first stab. I think my way may account for differences in point size of characters since I isolate each paragraph and determine the ht needed for that. So even if there is a character bigger than the rest, it will be taken into account for that paragraph. I recognize the surest way would be to do what you suggested. I have no intention of doing that nor of even trying my approach. Given the way I work, I think I'm close enough with the formula in my macro. Further, all that parsing would take time.

    As to the shown/edited vs displayed when hidden, I never appreciated the distinctions before.

    Thanks again.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •