VBA to edit a comment

[Brent E]

Good morning,

I am using Windows XP; Office 2002 SP2.
i am trying to put together some code that will:
A. Create a comment in the current cell (without naming the cell by range)
B. Enter the folling text double spaced and bolded
Analysis:
Result:
Reviewers:
C. And size the comment box to a specific size.

I recorded a macro as I performed these operations and got the following
code. (I replaced the cell range references w/ "Activecell" to make the cell
selection more dynamic.)

This is the code from Excel:
Sub Notes()
'
' Notes Macro
' Macro recorded 11/5/2007 by DD065C
'

'
ActiveCell.Select
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:= _
"DD065C:" & Chr(10) & "" & Chr(10) & "Analysis: " & Chr(10) & "" &
Chr(10) & "Result: " & Chr(10) & "" & Chr(10) & "Reviewers: "
'Selection.ShapeRange.ScaleWidth 1.8, msoFalse, msoScaleFromTopLeft
'Selection.ShapeRange.ScaleHeight 3.41, msoFalse, msoScaleFromTopLeft
'ActiveCell.Comment.Shape.Select True
ActiveCell.Comment.Text Text:= _
"DD065C:" & Chr(10) & "" & Chr(10) & "Analysis: " & Chr(10) & "" &
Chr(10) & "Result: " & Chr(10) & "" & Chr(10) & "Reviewers: "
End Sub

When I run the macro on a new cell, everything works except the lines I have
commented out:
'Selection.ShapeRange.ScaleWidth 1.8, msoFalse, msoScaleFromTopLeft
'Selection.ShapeRange.ScaleHeight 3.41, msoFalse, msoScaleFromTopLeft
'ActiveCell.Comment.Shape.Select True

These always trigger a debug error stating:
Runtime Error '438'. Object does not support this object or method.

Does anybody know why this is occuring and can recommend a correction?

Thanks,
Brent

 

[FSt1]

hi
see debra dalgleish's site on resize a comment;
http://www.contextures.com/xlcomments03.html#Resize

regards
FSt1

 

[Jim Rech]

I didn't run your code but the first two commented out lines work on the
'Selection'. Clearly the selection is supposed to be the comment box. Yet
a few lines before you make the comment Visible=False so it cannot be the
selected object. I'd start by moving that line to the end of the code.

--
Jim
| Good morning,
|
| I am using Windows XP; Office 2002 SP2.
| i am trying to put together some code that will:
| A. Create a comment in the current cell (without naming the cell by
range)
| B. Enter the folling text double spaced and bolded
| Analysis:
| Result:
| Reviewers:
| C. And size the comment box to a specific size.
|
| I recorded a macro as I performed these operations and got the following
| code. (I replaced the cell range references w/ "Activecell" to make the
cell
| selection more dynamic.)
|
| This is the code from Excel:
| Sub Notes()
| '
| ' Notes Macro
| ' Macro recorded 11/5/2007 by DD065C
| '
|
| '
| ActiveCell.Select
| ActiveCell.AddComment
| ActiveCell.Comment.Visible = False
| ActiveCell.Comment.Text Text:= _
| "DD065C:" & Chr(10) & "" & Chr(10) & "Analysis: " & Chr(10) & "" &
| Chr(10) & "Result: " & Chr(10) & "" & Chr(10) & "Reviewers: "
| 'Selection.ShapeRange.ScaleWidth 1.8, msoFalse, msoScaleFromTopLeft
| 'Selection.ShapeRange.ScaleHeight 3.41, msoFalse, msoScaleFromTopLeft
| 'ActiveCell.Comment.Shape.Select True
| ActiveCell.Comment.Text Text:= _
| "DD065C:" & Chr(10) & "" & Chr(10) & "Analysis: " & Chr(10) & "" &
| Chr(10) & "Result: " & Chr(10) & "" & Chr(10) & "Reviewers: "
| End Sub
|
| When I run the macro on a new cell, everything works except the lines I
have
| commented out:
| 'Selection.ShapeRange.ScaleWidth 1.8, msoFalse, msoScaleFromTopLeft
| 'Selection.ShapeRange.ScaleHeight 3.41, msoFalse, msoScaleFromTopLeft
| 'ActiveCell.Comment.Shape.Select True
|
| These always trigger a debug error stating:
| Runtime Error '438'. Object does not support this object or method.
|
| Does anybody know why this is occuring and can recommend a correction?
|
| Thanks,
| Brent
|
|

 

[Brent E]

Good day Fst1 and Jim,

Thanks for your posts.
Fst1, I dind't get to try the examples on the link you sent but it does look
like it would of worked and definitely would put me in the R. Direction.
Thanks for the great resource.

Jim, thanks for your suggestion as well. I have not tried this yet but will
keep that in mind. What confuses me about that tho is that the code came from
me recording a macro, so the lines and order of the code were written by
Excel itself. I was confused also why that even had the visible turned off as
well. I think your idea could work.

Solution: I noticed that even if I could get the sizing to work in the code
section, I needed certain sections to be bold and certain sections not to.
This did not seem to carry over in the macro even tho I copied and pasted
from a comment correctly formmated. So, what I found was that if I had a
certain cell that is hidden in a different place on the sheet, and set my
text for the cell and also a comment w/ the text and formatting I want, and
then set a macro to copy and paste that cell into the cell I want, then both
the cell text, comment and formatting all came through.

Thanks to you both for your suggestions.

Cordially