Auto resize a comment box

[Shadow123]

Hi all,

I have been messing with my code for a few days, and I'm just workign on the the presentation now. The code below works except for the resize of the comment box...

I tried recording it, and it gave me the below, but I want to make it automatic if i can. Failing that Id like to set all comment boxes in the sheet to the below sizes (rather than one at a time)

    Selection.ShapeRange.ScaleWidth 1.05, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.33, msoFalse, msoScaleFromTopLeft

   If MktCP = test Then
       With .Cells(r - 1, -15)
           '.Value = cell.Offset(, -12).Value
                 Set Cmt = .Comment
                   If Cmt Is Nothing Then
                      .AddComment Text:=cell.Offset(, -12).Value & " @ " & cell.Offset(, -6).Value
                        .Shape.TextFrame.AutoSize = True  'this line not working

                        Else
                                   
                         Cmt.Text Text:=Cmt.Text & " " & cell.Offset(, -12).Value & r & " "
                        .Shape.TextFrame.AutoSize = True 'this line not working

                       End If
             End With
             Else
            End If

Any help?

 

[Andrew Poulsom]

Maybe this will help you:

Excel Comments VBA

 

[Shadow123]

sigh, i googled all over for that, guess i should of added looked further. Thats done it, thanks very much.