Manipulate comments with macro

[Tom 54]

Hello,

Excel 2000 (Office 2000 on WinXP HE) allows me to record:

Sub Macro1()
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:="test"
Selection.ShapeRange.IncrementLeft 53.4 ' execution stops here
Selection.ShapeRange.IncrementTop 110.4
End Sub

But when I try to run it, I get:
Run Time Error '438':
Object doesn't support this property or method.

Error is generated on 1st line referencing "ShapeRange."

Is there a way to manipulate placement of comments in spreadsheet via
VBA?

Thanks for any help,
Tom54

[Robert Rosenberg]

Here's code to help you get started:

Sub AddComment()

Dim cmt As Comment

'''Add comment if it doesn't exist
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then Set cmt = ActiveCell.AddComment

'''Format the comment
With cmt
.Text "Test"
.Shape.AutoShapeType = _
msoShapeActionButtonCustom
With .Shape.DrawingObject.Font
.Name = "Comic Sans MS"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 50
End With
End With

End Sub

--
______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
"Tom 54" <lvt...@aol.com> wrote in message
news:3dadc209....@msnews.microsoft.com...

[Tom 54]

Hello Robert,

Thank you for your interest. I think, however, that I wasn't clear
enough regarding my issue. I do not have an problem creating a
comment. My problem is that I want to move existing comments with
VBA. You see, sometimes I want to hide columns but get the error
"cannot shift objects off page" because some user has entered a
comment that is in an inconvenient place. I would like to test for
the error, then move any or all offending comments to allow the macro
to fulfill its function.

The only method I've been able to find is:

Selection.ShapeRange.IncrementLeft 99.9

which is documented in VBA help and is how the action is recorded by
my version of XL. Unfortunately, the method is rejected (Run Time
Error '438': Object doesn't support this property or method.) when I
try to play it back.

Perhaps I don't understand something definitive in your response, but
it does not seem to address this issue.

Tom 54

On Wed, 16 Oct 2002 13:34:26 -0700, "Robert Rosenberg"
<bla...@email.msn.com> offered:

[acw]

Tom

A couple of things.
1) the shape cannot be selected unless it is visible. So
you have to either make it visible or not make it
invisible. So change the line to
Range("A1").Comment.Visible = True

2) You also have to select the comment. So insert the
line
Range("a1").comment.shape.select 'this will select the
shape
before the line
> Selection.ShapeRange.IncrementLeft 53.4

and you should be right.

Tony

>.
>

[Robert Rosenberg]

Yes, I misunderstood. Read too fast. My apologies.

The reason you encounter the error is because the Object Positioning setting
for Comments is "Don't move or size with cells". This can be found on the
Properties Tab of the Format-->Comments command.

You can easily work around this by changing the setting to "Move and size
with cells". This may cause some visible comments at the far right of the
worksheet to disappear until the hidden columns become visible again. A
small price to pay to avoid the indepth macro needed to identify which
comments need shifting and by how much to avoid the "Cannot shift" error.

The following macro changes the setting to "Move and size with cells" for
all of the comments in all worksheets for the current workbook. You can, of
course, alter the macro to your needs:

Sub SetMoveButDontSizeForAllComments()

Dim cmt As Comment
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
For Each cmt In wks.Comments
cmt.Shape.Placement = xlMoveAndSize
Next cmt
Next wks

End Sub

Hope this better helps solve your problem.

______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
"Tom 54" <lvt...@aol.com> wrote in message

news:trvrqu8vi403hugfi...@4ax.com...

[Tom 54]

The routine you provided was well worth the wait. I just place a call
to it from my sub that is likely to cause problems and everything
executes slick as snot on a doorknob. Can't imagine all the hours
this has saved me.

Tom54

[Tom 54]

Hello Tony,

Those two steps were exactly the insight I needed. Thanks for your
help.

Tom 54

On Wed, 16 Oct 2002 21:59:28 -0700, "acw" <ac_wa...@yahoo.com>
wrote:

[Robert Rosenberg]

Glad I finally got it right. <bg>

______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
"Tom 54" <lvt...@aol.com> wrote in message

news:3daec52e....@msnews.microsoft.com...