change text box size for a range of cells...

[sadams1]

I have the following to change the comment box size for one cell as follows:

Sub Comments_Formatonecell()

ActiveCell.Comment.Shape.Width = 540
ActiveCell.Comment.Shape.Height = 150

End Sub



Then this to do the same for all cells:

Sub Comments_Format()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Width = 800
cmt.Shape.Height = 450

Next cmt
End Sub




Is is possible to do the same for an active range?

 

[pgc01]

Hi

I guess that by an active range you mean a selected range.

Select a range and try:

Sub Comments_Format()
Dim r As Range

For Each r In Selection
    If Not r.Comment Is Nothing Then
        With r.Comment.Shape
            .Width = 800
            .Height = 450
        End With
    End If
Next r
End Sub

 

[sadams1]

Hi

I guess that by an active range you mean a selected range.

Select a range and try:

Sub Comments_Format()
Dim r As Range

For Each r In Selection
    If Not r.Comment Is Nothing Then
        With r.Comment.Shape
            .Width = 800
            .Height = 450
        End With
    End If
Next r
End Sub

SWEET! Many thanks!
On a similar note, is it possible to do something with textboxes like this? All the stuff I found dealt with autosizing to text. I'd like to active a textbox & have a macro that will make a specific size.
Thanks again!