View original (French)

Comment submission next to each cell

rubisdor -  
 rubisdor -
Hello,
I often put a lot of comments in the cells. I can move these comments around to avoid them hiding the content of the following cells. However, it has happened to me when saving and then opening the file that some comments appear completely elsewhere. For example: in A1 there's a data entry, I've added a comment on that cell and then I move it a bit further away on the sheet. I save the file and upon opening it, my comment is no longer in the place I had moved it to and ends up in, for example, cell H75, which forces me to move it again to a more appropriate location. When it’s only one cell, it’s not an issue for me, but when it comes to several hundred comments that are no longer next to their respective cells, I find this really frustrating.
How can we request to reposition all the comments next to the cells that contain them?
Thank you for your help.

2 answers

  1.
    Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
     
    Hello,

    With a macro in the sheet module (to be adapted): 
    Private Sub AlignComments() Dim rng As Range Dim cel As Range Dim cmt As Comment Set rng = Me.Range("A3:A200") ' to be adapted For Each cel In rng.Cells Set cmt = cel.Comment If Not cmt Is Nothing Then With cmt .Visible = True .Shape.Top = cel.Top + 1.5 .Shape.Left = [C1].Left + 18 ' to be adapted End With End If Next cel End Sub


    --
    Best regards
    Patrice
    1
    1. rubisdor
       
      First of all, thank you for your solution. Unfortunately, I don't know VBA, but I can guess what the little program does. I have no doubt that this is where I should find my solution to my problem.
      If I understand correctly, you define a subroutine by setting the variables rng, cel, Cmt. You ask the subroutine to work in the range A3 to A200, and I suppose this is the range to adapt. And to set my range, for example ("A1:Z1000")
      Then, if there is a comment, you make it visible. What does
      .Shape.Top = cel.Top + 1.5
      .Shape.Left = [C1].Left + 18 (what should I adapt here?)
      And I do not see why you put for shape.Top = cel.Top and for shape.Left= [C1]. And not also cel.Left and why +18?
      When I copy your little program into a macro and run it, it tells me that it is missing an end sub while I have it.
      Thank you for your precious help.
      0
    2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
       
      Hello,

      This small routine allows you to position the comments in the range A3:A200.
      You need to place it in the module of the sheet that contains the comments:
      - Select and copy the entire text of the macro above.
      - Right-click on the sheet tab / view code
      - Paste

      .Shape.Top = cel.Top + 1.5 sets the vertical position of the comment (i.e. the distance between the top edge of the comment and the top edge of the worksheet) = position of the cell containing the comment +1.5 (1.5 to slightly shift the comment down so that the arrow connecting it to the cell is horizontal)

      .Shape.Left = [C1].Left + 18 sets the horizontal position of the comment = the position of cell C1 + 18. This last command was suggested assuming all comments are in the same column A, it places the comments above column C which is empty.

      If your comments are in several different columns, it is more difficult to place them automatically in a range of non-empty cells.
      0
    3. rubisdor
       
      Thank you very much, Patrice, for your valuable help and clear explanations; you save me a lot of trouble. It's really nice to be able to rely on the help of much more experienced people who care about helping others.
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    it's not easy, ...otherwise to write the comment directly in the adjacent cell instead of inserting it.
    But why not hide them so that they are only visible one at a time when you select a cell?
    best regards

    0
    1. rubisdor
       
      Thank you, I had thought about it but my comments are very long and it's not very practical. If I can't find other solutions to solve this problem, I will have to resign myself to your solution.
      Best regards
      0