ExcelBanter - How do I re-size a comment box using VBA?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - How do I re-size a comment box using VBA? (https://www.excelbanter.com/excel-discussion-misc-queries/80805-how-do-i-re-size-comment-box-using-vba.html)

Pete at Sappi Fine Paper

How do I re-size a comment box using VBA?

Recording a macro does not help - it gives:

Selection.ShapeRange.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.22, msoFalse, msoScaleFromTopLeft

But replacing "Selection" with "Range("B" & 15).comment" does not work.

Any ideas, please? Thanks!

Pete

Andy Pope

How do I re-size a comment box using VBA?

Hi,

Try,

With Range("B" & 15).Comment.Shape
.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.22, msoFalse, msoScaleFromTopLeft
End With

Cheers
Andy

Pete at Sappi Fine Paper wrote:
Recording a macro does not help - it gives:

Selection.ShapeRange.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.22, msoFalse, msoScaleFromTopLeft

But replacing "Selection" with "Range("B" & 15).comment" does not work.

Any ideas, please? Thanks!

Pete

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Pete at Sappi Fine Paper

How do I re-size a comment box using VBA?

That worked!! Thanks Andy!

"Andy Pope" wrote:

Hi,

Try,

With Range("B" & 15).Comment.Shape
.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.22, msoFalse, msoScaleFromTopLeft
End With

Cheers
Andy

Pete at Sappi Fine Paper wrote:
Recording a macro does not help - it gives:

Selection.ShapeRange.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.22, msoFalse, msoScaleFromTopLeft

But replacing "Selection" with "Range("B" & 15).comment" does not work.

Any ideas, please? Thanks!

Pete

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

All times are GMT +1. The time now is 07:03 PM.