Is there a better way to include comments than "Insert Comment"?

[Abraxas365]

So I've got a bunch of comments in a spreadsheet and all the comments are of variable length. I try not to change the size of the default box if I can avoid it cause I've noticed if I increase the size or move it, the next time I look at that comment, the box has changed size and I have to change it back so I can read everything clearly.

I know I can change the format of the text boxes, but I don't think that helps me with my problem now. And I don't think it changes the format of the comments already in the spread sheet.

So is there something I can do to prevent these automatic and undesirable format changes?

Or is there a superior way to include comments in Excel that isn't so finicky?

The spreadsheet has "Sort and Filter" options at the top and I frequently change how I want the list to appear. Is that what's causing the format changes?

On a side note: This forum is a really wonderful source of information and everyone who contributes is really knowledgeable. Thank you for your help!

 

[Yongle]

1. What version of Excel are you using ?
( Notes were recently introduced into Excel )
2. When do you want to see each comment ?
( eg only when hovering over a cell ? )
3. How would you prefer to see the comment ?
( eg make comment box consistent width ? )
4. Are comments usually edited after initial creation ?
5. Are you happy to have some VBA in the worksheet to have a comment always look the way you want ?

 

[Abraxas365]

1) 2013.

2) Hovering over the cell might be nice. I would almost prefer clicking and opening a second, smaller table that might only have 2 or 3 columns of information (one of which is a comment that might have variable length).

3) Preferably a consistent width.

4) Yes.

5) Yes, but are the VBA's you have in mind supported by Excel 2013?

Basically, I'm currently managing 180 utility projects for construction. Each project has a row and all the rows are typically organized by the date of construction. These projects might require several material requisitions (MR's), each of which has its own number. If I could, I'd like to list all the MR's related to each project in one cell (so I can search for that MR if I need to) and be able to click on that cell and open a separate table which lists all the same MR's, along with a column for the status of that MR (INPROG, APPROVED, COMPLETE, CANCELED) and a brief description of the material in that MR. This information isn't important to view at a glance, but it is something I'd like to track.

Pivot Tables are kind of what I want, but I don't like the idea of whole new sheets popping up for this information. I'd prefer to just click on the cell with my MR's listed and have a smaller table pop-up, with 3 columns, and as many rows as I need (as we add/replace MR's to each project).

 

[Yongle]

Suggest new sheet created with all MR's for all projects (containing as many columns as necessary to hold the information required)
A userform could be used return details for the selected project, based on details from new sheet
(userform would be triggered from your current sheet, its contents driven by the active row)

 

[Yongle]

1.Comments in sheet "Projects" refreshed to include all MR's

2.Supporting sheet "Material Reqs" - data extracted from this sheet

3.Code used

Sub AddComment()
    Dim cel As Range, proj As Range, CommentText As String
    For Each proj In Sheets("Projects").Range("A2:A6")
         CommentText = ""
        For Each cel In Sheets("Material Reqs").Range("E2:E30")
            If cel = proj Then CommentText = CommentText & "  " & cel.Offset(, -4)
        Next cel
       
        With proj
            On Error Resume Next
            .Comment.Delete
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:=CommentText
            .Comment.Shape.TextFrame.AutoSize = True
            On Error GoTo 0
        End With
    Next proj
End Sub

4. I then added a userform triggered by Right-Click in cell A2

Let me know within the next few days if you want to proceed with this further
Thanks

 

[Yongle]

Are you wanting to pursue this further ?

 

[Yongle]

??