Situation: You have table rows containing short headings, and the cell next to it a long text description. You want to “convert” the description column into comments on the “heading” cells to make the table smaller or save vertical space. Maybe a two images explain that better.
So we want to go from “before” …
… to “after” …
Now this is the Excel VBA code to achieve this:
Sub convert_to_comment() ' convert_to_comment Makro ' Tastenkombination: Strg+k ' step 1 - create (VISIBLE!) comments ' visible because excel has a bug. yeah ... . Dim commentText As String ActiveCell.Comment.Delete commentText = ActiveCell.Offset(0, 1).Text ActiveCell.AddComment commentText ActiveCell.Comment.Visible = True ActiveCell.Comment.Shape.Width = 450 ActiveCell.Comment.Shape.Height = 150 ActiveCell.Offset(1, 0).Select ' make comments invisible in step 2 ' just comment all lines from step1, and uncomment all of the following ' lines, and re-run the macro with each cell. see notes below in the ' blog post. 'ActiveCell.Comment.Visible = False 'ActiveCell.Offset(1, 0).Select End Sub
- Excel does not seem to support auto-sized comments since v2016. Don’t get me started on how much I “dislike” this pile of … whatever.
- At least Excel v16.55 has a bug which makes this 2-step-approach necessary (otherwise the comment boxes just won’t show).