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
Some notes:
- 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).