Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to edit a comment
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to edit a comment
hi
see debra dalgleish's site on resize a comment; http://www.contextures.com/xlcomments03.html#Resize regards FSt1 "Brent E" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to edit a comment
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 "Brent E" wrote in message ... | 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 | | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to edit a comment
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 "Brent E" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit comment in cell | Excel Discussion (Misc queries) | |||
Comment box appears many rows away when trying to edit | Excel Discussion (Misc queries) | |||
Comment box appears many rows away when trying to edit | Excel Discussion (Misc queries) | |||
How can I edit a comment w/o first having to select Show Comment | Excel Discussion (Misc queries) | |||
cannot edit comment | Excel Discussion (Misc queries) |