Resize of comments box / shape etc

[ipbr21054]

Hi,
Please can you advise on my code as i have an issue i am unable to sort.

The shape is to small for the amount of text within it,ive tried to add like .Autoresize = False etc but just got errors.
Shape needs to expand to allow all the text to be read.

 For Each MyComments In ActiveSheet.Comments
            With MyComments
                .Shape.AutoShapeType = msoShapeRoundedRectangle
                .Shape.TextFrame.Characters.Font.Name = "Times Roman" 'FONT STYLE CORRECT
                .Shape.TextFrame.Characters.Font.Size = 20  'TEXT SIZE CORRECT
                .Shape.TextFrame.Characters.Font.ColorIndex = 1 'TEXT COLOR CORRECT
                .Shape.LINE.ForeColor.RGB = RGB(255, 0, 255)
                .Shape.LINE.BackColor.RGB = RGB(255, 255, 255)
                .Shape.Fill.Visible = msoTrue
                .Shape.Fill.ForeColor.RGB = RGB(0, 255, 255) ' FILL COLOR CORRECT
                .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
           End With
          Next 'comment

 

[Tom.Jones]

Hi,

Put this line inside With - End With

.Shape.TextFrame.AutoSize = True

 

[ipbr21054]

Hi,
It that codee correct as i see.

.Shape.TextFrame.AutoSize = True

 

[ipbr21054]

Hmmm
Something odd here see screenshot

 

[ipbr21054]

Now applied but doesnt seem correct.

True would send my text just left to right direction continuosly.
False would keep it within aa shape but in respect of going down direction but the shap stays the same size & some text then hidden.

I thought True would adjust to the amount of text

 

[Tom.Jones]

To enter the text below each other, use the Enter key.
I think the font size is too large. I replaced 20 with 12 and it's OK.

You can also try something like:
Let's say that for 100 characters you need a width of 250, and if it exceeds this number of characters then the width will be multiplied by 1.5 or 2.
You will also proceed with the height of the comment.

 

[NoSparks]

This works for me. Thankfully my eyesight isn't bad enough to need a font that large

Sub Adjust_Comments()
    Dim MyComments As Comment

For Each MyComments In ActiveSheet.Comments
    With MyComments
        .Shape.AutoShapeType = msoShapeRoundedRectangle
        .Shape.TextFrame.Characters.Font.Name = "Times Roman"
        .Shape.TextFrame.Characters.Font.Size = 20
        .Shape.TextFrame.Characters.Font.ColorIndex = 1
        .Shape.Line.ForeColor.RGB = RGB(255, 0, 255)
        .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
        .Shape.Fill.Visible = msoTrue
        .Shape.Fill.ForeColor.RGB = RGB(0, 255, 255)
        .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
        .Shape.TextFrame.AutoSize = True
    End With
Next    'comment

End Sub

 

[ipbr21054]

Can’t use enter as the text is being sent to worksheet from userform.
Something isn’t right as with NoSparks still I see it no enlarge.

I write on my userform and command button sends values from text boxes to worksheet.
All are placed. In the correct cells.
The text I write is inserted as a comment fine.
So now when I put the cursor over the comment cell in question I see the text but some is outside of the comments frame.

Text text size 20 was left in by mistake. Actually I’m going to use 12

I was trying to get the code to just apply itself to the comment last sent from userform.
By having it at 22 I was able to see if the code was just beeing applied to one cell or all cells.

 

[NoSparks]

How about sharing a sample workbook so we're both dealing with the same thing.

 

[ipbr21054]

@NoSparks

Here