Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Comments - stop resizing and moving comments

When hiding and un-hiding rows, or moving rows, or having freeze panes on, the comments on cells for no reason stretch or move far down one's sheet. It would be really nice if the size of the comment stays as is and it doesn't move all over.

55 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Jan Wx, NZ shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

21 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • FaceTheMusic commented  ·   ·  Flag as inappropriate

    This bug exists since Excel 2003 if my memory serves. It still haven't been fixed after 15 years. We still need a macro to fix comment bugs. How ridiculous!

  • ExcelPasswordRecovery commented  ·   ·  Flag as inappropriate

    Get wonderful Excel Password Recovery Software to recover excel password and gives you complete permission to re-access excel file. By taking help of Excel Password Unlocker Software users can unlock excel file, word document file and access file. This software works on all excel file versions included – 97, 2000, 2002, 2003, 2007, 2010, 2013 and 2016.

    Read More:- http://excel-password-recovery-software.over-blog.com/

  • IF commented  ·   ·  Flag as inappropriate

    Col Delane,
    Thanks for the great macro...just having one issue. For some reason when I run it, it gets stuck with "Adjusting comments on..." in the Status Bar, and doesn't actually change anything with the comments. I've tried waiting several minutes (and it's a very minimal spreadsheet), but the status continues Adjusting but not making changes. The result is the same whether I choose All Sheets or Active Sheets. I've also tried it in an xls (rather than xlsm). I can work in the spreadsheet without issue, I just have to close it to disable the macro/status.

    FYI, if I run Faith Granger's original, I get a run-time error -- Method 'AutoSize' of object 'TextFrame' failed.

    I'm not too familiar with VBA or Macros, so maybe it's something simple? Using the latest version of Excel for Mac. Thanks:)

  • Bob commented  ·   ·  Flag as inappropriate

    Col Deline:
    I confirm that the code provided below met my needs as well. I, too, was able to easily modify it for my application. I am grateful for the generous assistance.

  • Eric Tabor commented  ·   ·  Flag as inappropriate

    Col Delane:
    Thank you very much for the below macro language. I was able to understand it and modify it to give me exactly what I wanted: relocation of comments after hiding/unhiding rows. Thanks again for your time and thought, I greatly appreciate it.

  • Bob commented  ·   ·  Flag as inappropriate

    Col Delane,

    At this point, I only want to acknowledge receipt of the further recommendation and my gratitude for your prompt attention to the question. I'll work with it over the upcoming holiday to install and modify it into my main application.

    Thanks again. I never cease to be amazed at the generous way that so many share their expertise.

  • Col Delane commented  ·   ·  Flag as inappropriate

    Eric & Bob: My apologies, the post I referenced must have been the original code by Faith Granger, which I (& probably with the assistance of others) have since modified or replaced with other code. It's hard to keep track of time and tweeks made to code tools. :-)

    Try this, which also gives the user the option to process only the active worksheet, or all worksheets (excluding Chart sheets which cannot have cell comments).

    Sub Resize_Relocate_CellComments()

    ' Purpose: Adjust size of all cell comment boxes on the active sheet of the active workbook to match the comment text therein, and locate it proximate to the host cell

    'Define Procedure Variables
    Dim wbk As Workbook
    Dim wks As Worksheet
    Dim cmt As Comment
    Dim lArea As Long


    On Error GoTo ErrorHandler

    Set wbk = ActiveWorkbook
    Set wks = ActiveSheet

    Select Case MsgBox("Click:" & vbLf & vbLf & "YES to review & fix comments on ALL sheets in the Active WORKBOOK," & vbLf & vbLf & "NO to review & fix comments on the Active SHEET only, or" & vbLf & vbLf & "CANCEL to abort this process.", vbYesNoCancel Or vbQuestion Or vbDefaultButton2, "Cell Comments Resize and Relocation")

    Case vbYes

    For Each wks In wbk.Worksheets
    Application.StatusBar = "Adjusting comments on " & wks.Name
    For Each cmt In wks.Comments
    With cmt
    .Shape.TextFrame.AutoSize = True
    If .Shape.Width > 300 Then
    lArea = .Shape.Width * .Shape.Height
    .Shape.Width = 200
    .Shape.Height = (lArea / 200) * 1.1
    End If
    .Shape.Top = cmt.Parent.Top + 5
    .Shape.Left = .Parent.Offset(0, 1).Left + 5
    End With
    Next cmt
    Next wks

    Case vbNo

    Application.StatusBar = "Adjusting comments on " & wks.Name
    For Each cmt In wks.Comments
    With cmt
    .Shape.TextFrame.AutoSize = True
    If .Shape.Width > 300 Then
    lArea = .Shape.Width * .Shape.Height
    .Shape.Width = 200
    .Shape.Height = WorksheetFunction.Min((lArea / 200) * 1.1, 100)
    End If
    .Shape.Top = cmt.Parent.Top + 5
    .Shape.Left = .Parent.Offset(0, 1).Left + 5
    End With
    Next cmt

    Case vbCancel
    Exit Sub

    End Select

    Application.StatusBar = False
    MsgBox "All cell comments resized and located proximate to their parent cell.", vbOKOnly, "Cell Comment Autosizing & Relocator"

    ExitPoint: '---------------->>>>>>>>>-------------------->>>>>>>>>-------------------->>>>>>>>> Exit Sub
    On Error GoTo 0
    Exit Sub

    ErrorHandler:
    Resume ExitPoint

    End Sub
    '-----------------------------------------------------------

  • Bob commented  ·   ·  Flag as inappropriate

    Eric Tabor, thanks for taking up this issue. I've also been dinking around with the code referenced by Col Delane and agree that it does resize, but does not seem to relocate the comment closer to "home". Not that I'm "fluent" in coding, but as I read, re-read, and step through the macro, I'm at a loss to see how it even addresses the location of the comment. Can anyone help me understand how/where that is alleged to be taking place?

  • Eric Tabor commented  ·   ·  Flag as inappropriate

    Col Delane: Thanks for the response, but I tried again and the macro does indeed resize the comments, but does not relocate them back to being adjacent to the "parent" cell (when in comment edit mode).

    The macro we are discussing is as follows (from Faith Granger):

    Sub Comments_Size_to_600X600()
    'posted by FaithGranger 2014-08-06
    Dim MyComments As Comment
    Dim lArea As Long

    For Each MyComments In ActiveSheet.Comments
    With MyComments
    .Shape.TextFrame.AutoSize = True
    If .Shape.Width > 0 Then
    lArea = .Shape.Width * .Shape.Height
    .Shape.Width = 600
    ' An adjustment factor of 1.1 seems to work ok.
    .Shape.Height = (lArea / 200) * 1.1
    End If
    If .Shape.Height < 600 Then
    .Shape.Height = 600
    End If
    End With
    Next ' comment

    End Sub

    If anyone does have the applicable macro language to relocate comments back to being adjacent to the "parent" cell, I would greatly appreciate it. I do not need to resize my comments, just relocate after hiding/unhiding rows.

  • Col Delane commented  ·   ·  Flag as inappropriate

    Eric Tabor: The VBA by Faith Granger which I referenced in the very first comment to this post both resizes and relocates cell comments.

  • Eric Tabor commented  ·   ·  Flag as inappropriate

    I tried the macro (from Kenneth, as posted by Faith Granger), but it only re-sized the comment boxes, did not re-locate them back adjacent to the cells they "belong to". I have not learned to program macros (VBA). Is there a macro that will re-locate the comments?? Re-location back to being adjacent to the corresponding cell is the only thing I need the macro to do.

  • KC commented  ·   ·  Flag as inappropriate

    I would also like to see you improve bugs in Excel before moving on to bigger flashier things. Like stopping the comment boxes from moving to the bottom or top of the spreadsheet and becoming very small. I currently have to use a macro to correct this. Sorry to complain, you have a really great product here. It would also be nice to see you add the features that make like easier seen on AbleBits and Kutools add-ins. I just say, to make Excel more practical for everyday work, and fixing bugs while also working on the bigger items. Excel is a very good product

  • Jan Wx, NZ commented  ·   ·  Flag as inappropriate

    Hi Kenneth

    Are you able to save as xlsx please. This surely doesn't need macro's? I fully agree with your last comment !!

  • Jan Wx, NZ commented  ·   ·  Flag as inappropriate

    Hi Kenneth, that's not a real work-around one is better of VBA-ing as per Col's comment. Much better would be to have a fix. The object Comment knows it's parent (the cell) and thus the parent's location. Anyway we are second guessing all of that.

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    OK, so there is a workaround. Use data validation input messages as your objectless comment. Then apply some sort of formatting to such cells to make them stand out. The "comment" will only appear if that cell is activated.

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    The problem is that comments are an object on the sheet. It's a similar thing for graphs. If you insert a row within a graph, the graph expands.

    I feel like comments should be a cell property, where the text can vary but their position and box size are automatically determined. We don't necessarily have to get rid of the old comment style, just add to it (e.g. "plain comment" versus "rich-text comment").

    It's like how MP3 files all have some space for lyrics.

  • Bob commented  ·   ·  Flag as inappropriate

    I'd be grateful if I could change the default setting on "Format Comment" Properties to either of the other two "Object positioning" options when I start a new workbook.

  • Jan Wx, NZ commented  ·   ·  Flag as inappropriate

    Very clever Col, but it shouldn't be needed in the first place :)
    For now I'll take your lead though as I doubt this will be fixed soon

← Previous 1

Feedback and Knowledge Base