57 votes21 comments · Excel for Windows (Desktop Application) » Graphics · Flag idea as inappropriate… · Admin →
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.
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):
'posted by FaithGranger 2014-08-06
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
.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
If .Shape.Height < 600 Then
.Shape.Height = 600
Next ' comment
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.Eric Tabor supported this idea ·
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.