Autosize a comment

**gtmeloney** · 06-03-2009, 03:51 PM

my macro processes a lot of data and puts a comment in certain cells (several thousand of them). I would like to autosize the comments so that the text can all be read by default.

I found the following on the web:

    
With Worksheets("Budget").Range("A1").AddComment
        .Text x
        .Visible = True
        .Shape.AutoShapeType = msoShapeRoundedRectangle
    End With
    
    Worksheets("Budget").Range("A1").Comment.Shape.Select True

   With Selection
        .AutoSize = True
    End With

My problem is that this requires the cell with the comment in it to be active AND VISIBLE. Is there any way that I can apply the autosize without having the cell VISIBLE?

The reason is that I have 2 worksheets that have comments on them, and I don't want to continually flicker between them activating cells

**gtmeloney** · 06-03-2009, 04:22 PM

Found it.

   
 For Each x In Worksheets("Budget By Month").Comments
        With x.Shape.TextFrame
            .AutoSize = True
        End With
    Next x

does the trick quickly without having to activate each cell individually.

**Leith Ross** · 06-03-2009, 04:30 PM

Hello gtmeloney,

Here ia macro that will auto size the comment. If the cell doesn't contain a comment then one is added, otherwise the text is replaced.

Sub CommnetTest()

  Dim Cmnt As Comment
  Dim Rng As Range
  Dim Txt As String
  
    Txt = "Comment test text."
    
    Set Rng = Worksheets("Sheet1").Range("A1")
    Set Cmnt = Rng.Comment
    
   'Check if cell has a comment
    If Cmnt Is Nothing Then
       Rng.AddComment Txt
       Set Cmnt = Rng.Comment
    End If
    
    With Cmnt
      .Text Txt
      .Shape.AutoShapeType = msoShapeRoundedRectangle
      .Shape.TextFrame.Characters.Text = ""
      .Shape.TextFrame.AutoSize = True
      .Shape.TextFrame.Characters.Text = Txt
    End With
    
End Sub