Use VBA to resize a note entry

[trevorc]

Hi All,


I'm trying to resize a note field using VBA, i recorded a macro to do what i wanted but if fails after adding the note.

The code below is what the recorded macro is for adding and resizing a basic note.
Any idea's ???

Sub Macro18()
    Range("W12").AddComment
    Range("W12").Comment.Visible = False
    Range("W12").Comment.Text Text:="Me:" & Chr(10) & ""
    Selection.ShapeRange.ScaleWidth 1.95, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 2.12, msoFalse, msoScaleFromTopLeft
End Sub

[trevorc]

With a lot of trial and many errors i found this works.

    Intersect(ActiveCell.EntireRow, tblColumns("Notes").Range).Comment.Shape.Width = 600
    Intersect(ActiveCell.EntireRow, tblColumns("Notes").Range).Comment.Shape.Height = 800

[p45cal]

Could this be a bit more straightforward?:

Sub Macro18()
With Range("W12").AddComment
  .Visible = False
  .Text Text:="Me:" & Chr(10)
  .Shape.Width = 600
  .Shape.Height = 800
End With
End Sub