Moving, Resizing a Cell Comment
 

Debra Dalgleish has tons of sample code:
http://contextures.com/xlcomments03.html
maybe even:
http://contextures.com/xlcomments03.html#Reset



Perico wrote:

I succeeded in iterating through a named range to insert comments and change
their font size. But I can't figure out how to move the comment frame or
resize it. The comment extends on one line off to the right and cannot be
seen completely. I can't use autosize b/c that just gives me one long
comment off the screen.

When I implement recorded macro code to do this, I get error messages, "not
supported". (I understand the height / width properties are relative to
current position.)
How can I either reposition the comment textframe or resize it, without
using the autosize method, and avoid error messages?

--

Dave Peterson

Moving, Resizing a Cell Comment
 

It will if you remove the 500000 values.

NickHK

"Perico" wrote in message
...
But why doesn't this code move my comment to the top and to
the left of the cell:

Sub RePosComments()
Dim mycell As Range
Dim myRng As Range
Sheets("AllDet").Activate
Range("AS27").Select
Set myRng = Selection

For Each mycell In myRng.Cells
If Not (mycell.Comment Is Nothing) Then
With mycell.Comment
.Shape.Top = .Parent.Offset(0, 1).Top + 500000
.Shape.Left = .Parent.Offset(0, 1).Left - 500000
End With
End If
Next
End Sub

"Dave Peterson" wrote:

Debra Dalgleish has tons of sample code:
http://contextures.com/xlcomments03.html
maybe even:
http://contextures.com/xlcomments03.html#Reset



Perico wrote:

I succeeded in iterating through a named range to insert comments and
change
their font size. But I can't figure out how to move the comment frame
or
resize it. The comment extends on one line off to the right and
cannot be
seen completely. I can't use autosize b/c that just gives me one long
comment off the screen.

When I implement recorded macro code to do this, I get error messages,
"not
supported". (I understand the height / width properties are relative
to
current position.)
How can I either reposition the comment textframe or resize it,
without
using the autosize method, and avoid error messages?

--

Dave Peterson

Moving, Resizing a Cell Comment
 

Debra's code used +5 in both lines just to "nudge" it away from that cell.

Perico wrote:

But why doesn't this code move my comment to the top and to
the left of the cell:

Sub RePosComments()
Dim mycell As Range
Dim myRng As Range
Sheets("AllDet").Activate
Range("AS27").Select
Set myRng = Selection

For Each mycell In myRng.Cells
If Not (mycell.Comment Is Nothing) Then
With mycell.Comment
.Shape.Top = .Parent.Offset(0, 1).Top + 500000
.Shape.Left = .Parent.Offset(0, 1).Left - 500000
End With
End If
Next
End Sub

"Dave Peterson" wrote:

Debra Dalgleish has tons of sample code:
http://contextures.com/xlcomments03.html
maybe even:
http://contextures.com/xlcomments03.html#Reset



Perico wrote:

I succeeded in iterating through a named range to insert comments and change
their font size. But I can't figure out how to move the comment frame or
resize it. The comment extends on one line off to the right and cannot be
seen completely. I can't use autosize b/c that just gives me one long
comment off the screen.

When I implement recorded macro code to do this, I get error messages, "not
supported". (I understand the height / width properties are relative to
current position.)
How can I either reposition the comment textframe or resize it, without
using the autosize method, and avoid error messages?

--

Dave Peterson


--

Dave Peterson

Moving, Resizing a Cell Comment
 

I tried smaller numbers and it didn't seem to do anything. What's peculiar
is I'd run the code, trying different numbers, and the only time the comment
frame appeared to have moved position was when I went into EDIT COMMENT,
after right clicking the cell.

"Dave Peterson" wrote:

Debra's code used +5 in both lines just to "nudge" it away from that cell.

Perico wrote:

But why doesn't this code move my comment to the top and to
the left of the cell:

Sub RePosComments()
Dim mycell As Range
Dim myRng As Range
Sheets("AllDet").Activate
Range("AS27").Select
Set myRng = Selection

For Each mycell In myRng.Cells
If Not (mycell.Comment Is Nothing) Then
With mycell.Comment
.Shape.Top = .Parent.Offset(0, 1).Top + 500000
.Shape.Left = .Parent.Offset(0, 1).Left - 500000
End With
End If
Next
End Sub

"Dave Peterson" wrote:

Debra Dalgleish has tons of sample code:
http://contextures.com/xlcomments03.html
maybe even:
http://contextures.com/xlcomments03.html#Reset



Perico wrote:

I succeeded in iterating through a named range to insert comments and change
their font size. But I can't figure out how to move the comment frame or
resize it. The comment extends on one line off to the right and cannot be
seen completely. I can't use autosize b/c that just gives me one long
comment off the screen.

When I implement recorded macro code to do this, I get error messages, "not
supported". (I understand the height / width properties are relative to
current position.)
How can I either reposition the comment textframe or resize it, without
using the autosize method, and avoid error messages?

--

Dave Peterson


--

Dave Peterson