Add comment, and move up

[Carroll]

I am using VBA to add a comment to a cell, fill the comment with text,
and size the comment's width and height. Unfortunately, the cell is
close to the bottom of the screen, and I would like to be able to move
the comment up somewhat so the entire comment can be read without
having to scroll down. I can record the movement of the comment up,
but that code doesn't work when I attempt to fit it into my VBA. I
thought one of these might work:
ActiveCell.Comment.Shape.IncrementTop -100# or
ActiveCell.Comment.Shape.IncrementTop -500
When I ran it, it didn't complain, but it also didn't do anything.
Does anyone have any ideas?

Thanks,
Carroll Rinehart

 

[Geoff]

Hi Carroll
At the risk of suggesting something you have already observed, using
InCrementTop -100 will only 'do' something if you put comments into View mode
from the View menu.
I tried TopLeftCell but that is Read Only.

Geoff

 

[Carroll]

Geoff,

I have basically created a form of sorts, but using cells to display
the data. My intent is to show all the info for one record on one
screen, without having to scroll. Not a big deal believe me. I could
just leave it as is, but it's more work for the user (granted, not a
lot). The comment is for a cell at the bottom right-hand corner of
the screen, and when the text in the column is more than what I
normally expect, part of the comment is below the bottom of the
current screen view.

By the way, I have Excel 2003.

I can drag a comment box with the macro recorder on, and when I do
that it shows:
Selection.ShapeRange.IncrementLeft -8.25
Selection.ShapeRange.IncrementTop -29.25
But if I attempt to use this code in my VBA, it doesn't like it. In
particular, it doesn't like ShapeRange, but it doesn't complain with
Shape.IncrementTop -29.25. But then again, it doesn't use it at all.
I've tried increasing the amount but still can't observe any movement.

Carroll

 

[Geoff]

Hi Carroll
I appreciate your comments, pun intended. I too have recently been
improving my comment coding. From experiment I can tell you the only way the
IncrementTop property seems to show any change is, as I have said earlier, to
set Comments to viewable from the View menu.
The problem with that of course is you will see all comments and also
requires the user to set the view. If your users are anything like my son
then it is all about minimalist movement of hand and eye and "unnecessary"
movements of the mouse are defined in millimetres. <g>

Geoff

 

[Carroll]

Thanks Geoff.

 

[Geoff]

FWIW
I've taken to using this for a change and for this post included the
property mentioned:

For Each com In Cells.SpecialCells(xlCellTypeComments)
With com.Comment.Shape
With .TextFrame
With .Characters.Font
.Name = "comic sans ms"
.Size = 12
.Color = vbBlack
End With
.AutoSize = True
End With
.Fill.ForeColor.RGB = vbCyan
.IncrementTop 50
End With
Next

Of course IncrementTop will apply to ALL comments at that point and also as
the name suggests it is incremental from its last position. I'm afraid I
haven't been able to do anything beyond that.

Geoff