Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add comment, and move up
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add comment, and move up
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add comment, and move up
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add comment, and move up
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add comment, and move up
Thanks Geoff.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add comment, and move up
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 "Carroll" wrote: Thanks Geoff. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move Comment into cell as Text | Excel Discussion (Misc queries) | |||
Move comment into cell as text | Excel Discussion (Misc queries) | |||
What would cause the comment box to resize and move on it's own? | Excel Worksheet Functions | |||
Is there a way to move a hidden comment on a worksheet? | Excel Discussion (Misc queries) | |||
How can I move the comment box to the left of the cell? | Excel Worksheet Functions |