Comment box disappears or moves

[AndiH]

I've inserted comments in many cells to use as an audit trail on my spreadsheet. Now when I open the spreadsheet, many of the comment boxes have closed up and moved far away from the original cell. All I can see is the call-out box line.

Is there a way to reformat all of them to restore and maintain the size of the comment box or do I have to do this one cell at a time? Argghh. Also, is there anything I can do to avoid this in the future?

I've seen threads which suggest changing "Tool Tips" in Windows but I can't find them in Windows 2003 and am uncertain this would be the solution anyway.

 

[Colin Legg]

Hi,

Save a back-up copy of your workbook (just in case).

Right click on the sheet tab and choose view code.

Paste in this code:

Sub FixComments()
    Const lngBUFFER As Long = 10
    
    Dim cmt As Comment
    
    For Each cmt In Comments
        cmt.Shape.Left = cmt.Parent.Left + cmt.Parent.Width + lngBUFFER
        cmt.Shape.Top = cmt.Parent.Top - lngBUFFER
        cmt.Shape.Placement = xlFreeFloating
    Next cmt
    
End Sub

Then left click anywhere inside the procedure so the cursor is flashing inside the procedure and press F5 to run it.

 

[AndiH]

Thank you Colin. I'm half-way there. It looks as if the comments have re-aligned to the appropriate cells but the comment text box itself is still closed.
A

 

[Colin Legg]

We can change the size of the comments within the procedure - I'm "working blind" here so my offering is:

Sub FixComments()
    Const lngBUFFER As Long = 10
    
    Dim cmt As Comment
    
    For Each cmt In Comments
        cmt.Shape.TextFrame.AutoSize = True
        cmt.Shape.Left = cmt.Parent.Left + cmt.Parent.Width + lngBUFFER
        cmt.Shape.Top = cmt.Parent.Top - lngBUFFER
        cmt.Shape.Placement = xlFreeFloating
    Next cmt
    
End Sub

 

[pecochran1]

Colin, thank you this seems to have corrected the comment issues in my worksheet. THANK YOU!!!

We can change the size of the comments within the procedure - I'm "working blind" here so my offering is:

Sub FixComments()
    Const lngBUFFER As Long = 10
    
    Dim cmt As Comment
    
    For Each cmt In Comments
        cmt.Shape.TextFrame.AutoSize = True
        cmt.Shape.Left = cmt.Parent.Left + cmt.Parent.Width + lngBUFFER
        cmt.Shape.Top = cmt.Parent.Top - lngBUFFER
        cmt.Shape.Placement = xlFreeFloating
    Next cmt
    
End Sub