Excel comments not doing what i require

[ipbr21054]

Ive been playing hours on this but not getting anywhere.
Can you advise please where im going wrong.

This code below is currently in use.
I see the comments box/shape etc but altering the Font size nothing changes.
Font isnt Calibri
Shape Style Preset13 is blue at all.

Ive attached a screenshot of the comments box.
I get no run time errors etc so why is the code saying how it should be but the output is anythng but ?

    If Len(sText) > 0 Then
        With oShape.TextFrame
            .Characters.Text = sText
            .Characters.Font.Name = "Calibri"
            .Characters.Font.Size = 12
            .Characters.Font.Bold = True
            .HorizontalAlignment = x1HAlignCenter
            .VerticalAlignment = x1VAlignCenter
            Shape.ShapeStyle = msoShapeStylePreset13
        End With
    End If

 

[Yongle]

how is oShape set in your code?

this works for me
- test on an empty sheet, select one or more cells and run code

Sub FormatComments()
    Dim sText As String
   
    Dim Cel As Range
    Application.ScreenUpdating = False
    For Each Cel In Selection
        sText = Cel.Address(0, 0) & Chr(10) & Date & Chr(10) & Time
        On Error Resume Next
        Cel.AddComment
        On Error GoTo 0
        With Cel.Comment.Shape
            With .Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 255, 190)
                .OneColorGradient msoGradientDiagonalDown, 1, 0.95
            End With
            With .Line
                .ForeColor.RGB = RGB(100, 100, 100)
                .Style = msoLineThickBetweenThin
            End With
            With .TextFrame
                .HorizontalAlignment = xlHAlignCenter
                .VerticalAlignment = xlVAlignCenter
                .Characters.Text = sText
                With .Characters.Font
                    .Size = 12
                    .Name = "Calibri"
                    .Bold = True
                End With
            End With
            .AutoShapeType = msoShapeRoundedRectangle
        End With
    Next Cel
End Sub

But the code always fails with .ShapeStyle = msoShapeStylePreset13

 

[ipbr21054]

Morning,
Thanks for the reply but there must be something else going on with my code as the same happens.

When i change the RGB color i see no change on my worksheet.

I have supplied a copy of my worksheet here for you to see,please do the following.

On the wprksheet POSTAGE select OPEN FORM
Enter values in the textboxes, CUSTOMER, ITEM DESCRIPTION, EBAY USERNAME, TRACKING NUMBER then write some text in THAT LONG LOWER TEXTBOX.

Now select POSTAGE SHEET TRANSFER BUTTON

Select YES
Select OK
Select NO

Now select CLOSE FORM

Now look in the cell of last row column D

This is where the comment should be as per your code but it isnt.

Many thanks for your time.

Download My Excel File Here

 

[ipbr21054]

Testing it on a new sheet i see it working.
Thus i select a cell the code runs and i see the time etc.

Now need to edit is so it applies to my text when sent to cell at column D

I need to apply the code so the text is taken from TextBox10 & not the selected cell on worksheet

 

[ipbr21054]

Basically my code works in respect of me typing on the userform then sending it to my worksheet.

What i need only to be done is to format the look / style of the comments box itself,the rest has been done