Autosize Cell Comments/Shapes via Macro Code

StefanG · Aug 24th 2006

Hi,
Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.

Code

Range("A14").Select
    Range("A14").AddComment
    Range("A14").Comment.Visible = False
    Range("A14").Comment.Text Text:="Ladida"
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .ReadingOrder = xlContext
        .Orientation = xlHorizontal
        [COLOR="Red"].AutoSize = True[/COLOR]
    End With

Display More

I just want to add

Code

.AutoSize = True

or something to that effect to the bit below. Any suggestions?

Code

Range("A14").Comment.Text "Ladida"

Thank you,
Stefan

**Andy Pope** · Aug 24th 2006

Re: autosize comment errors with recorded macro

The selection is not refering to the shape textframe.

Try this
[vba] With Range("A15")
.AddComment
With .Comment
.Visible = False
.Text Text:="Ladida"
With .Shape
With .TextFrame
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = msoTextOrientationHorizontal
.AutoSize = True
End With
End With
End With
End With
[/vba]

StefanG · Aug 25th 2006

Re: autosize comment errors with recorded macro

Hi Andy,

Quote

The selection is not refering to the shape textframe.

Thank you. - Wonder why the Macro Recorder is doing such garbage? - On the other hand, i suppose that there is not a "1-line approach" to autosizing the comment-box.
Stefan

**Andy Pope** · Aug 25th 2006

Re: Autosize Cell Comments/Shapes via Macro Code

To just autosize you can use
[vba]ActiveCell.Comment.Shape.TextFrame.AutoSize = True[/vba]

The macro recorder did not exactly record garbage. It just missed the 1 line of code which would have made the final block of selection code valid. That line is the one where you select the actual comment.

Dave Hawley · Aug 25th 2006

Re: Autosize Cell Comments/Shapes via Macro Code

StefanG, keep in mind that the macro recorder only records what you do via the GUI. Much of the code generated is often superfluous.

StefanG · Aug 25th 2006

Re: Autosize Cell Comments/Shapes via Macro Code

Andy,
Thank you for your help and explanation, much appreciated.

Dave,
Thank you for the heads up and for adjusting the thread title.

Stefan

Autosize Cell Comments/Shapes via Macro Code

Participate now!

Share