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

 

[NoSparks]

Does changing this

    If TextBox6.Value = "" Then
         .Cells(lastrow + 1, 4).Value = "NOTE"
         With ActiveSheet.Range("D" & lastrow + 1).Comment.Shape
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
    Else
         .Cells(lastrow + 1, 4).Value = TextBox6.Text
    End If

to this help any? The "Nothing here" is so I can see something in the comment.

    If TextBox6.Value = "" Then
         .Cells(lastrow + 1, 4).Value = "NOTE"
            Set MyComments = ActiveSheet.Range("D" & lastrow + 1).AddComment("Nothing here")
            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
    Else
         .Cells(lastrow + 1, 4).Value = TextBox6.Text
    End If

 

[ipbr21054]

Hi,

See screenshots for outcome

To see something in the comment do this.

Click blue open form

 

[NoSparks]

Sorry buddy, I don't know how to fix something when it works for me, I don't get any error.
I downloaded your file again in case I had inadvertently changed something else and replaced the code as per post 11 and it works straight away.

 

[ipbr21054]

Thanks.
Very strange
Can you send back the file shown which works for you and let me then add a comment and see what happens.

 

[NoSparks]

Box

app.box.com

 

[ipbr21054]

Morning,
Ive tried to open the file that was just downloaded but takes ages to open.
Once open i click on the command button to open the form but see the following error.

Is the file you uploaded the one you had used as strange this has appeared if it was ok for you

 

[NoSparks]

Morning ???
it's around 1:30 am here, can't sleep tonight.

Don't know what to tell you, it works fine for me.
I did notice the file you uploaded was 3.3meg and my Excel 2010 saved it at 1.6meg.
Sorry but I'm afraid that's all the help I can offer with this.

 

[ipbr21054]

Ok,

Many thanks anyway