Feedback by UserVoice

Eric Tabor

My feedback

  1. 57 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Eric Tabor commented  · 

    Col Delane:
    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.

    Eric Tabor commented  · 

    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):

    Sub Comments_Size_to_600X600()
    'posted by FaithGranger 2014-08-06
    Dim MyComments As Comment
    Dim lArea As Long

    For Each MyComments In ActiveSheet.Comments
    With MyComments
    .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
    End If
    If .Shape.Height < 600 Then
    .Shape.Height = 600
    End If
    End With
    Next ' comment

    End Sub

    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  · 
    Eric Tabor commented  · 

    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.

Feedback and Knowledge Base