VBA change comment field excel

[MissEcxel]

Hi all,
I'm rather new to excel VBA actually a newbie in using vba,
I recorded some things i want to automatically change on all comments.
Can someone please help explaining why this code isn't working on all my comment cells?

Sub Comments_changemargin()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Name = "Cambria"
.FontStyle = "Regular"
.Size = 11
.StrikeThrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
.AutoSize = True
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 1
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(186, 179, 163)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
End With
Selection.ShapeRange.TextFrame.MarginLeft = 1.42
Selection.ShapeRange.TextFrame.MarginRight = 1.42
Selection.ShapeRange.TextFrame.MarginTop = 1.42
Selection.ShapeRange.TextFrame.MarginBottom = 1.42
End Sub
Next 'comment
End Sub

Thank you very much for all the help in advance.

 

[Andrew Poulsom]

Welcome to MrExcel.

To use Selection you would need to Select each Comment within your loop. Try replacing Selection with MyComments.

 

[MissEcxel]

Thank you very much for the response,

I changed it to:

Sub Comments_change()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments.Font
.Name = "Cambria"
.FontStyle = "Regular"
.Size = 11
.StrikeThrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With MyComments
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal

.AutoSize = True
End With
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.Solid
.ShapeRange.Fill.ForeColor.SchemeColor = 1
.ShapeRange.Fill.Transparency = 0#
.ShapeRange.Line.Weight = 0.75
.ShapeRange.Line.DashStyle = msoLineSolid
.ShapeRange.Line.Style = msoLineSingle
.ShapeRange.Line.Transparency = 0#
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.RGB = RGB(186, 179, 163)
.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
With MyComments
.Placement = xlMoveAndSize
.PrintObject = True
End With
.ShapeRange.TextFrame.MarginLeft = 1.42
.ShapeRange.TextFrame.MarginRight = 1.42
.ShapeRange.TextFrame.MarginTop = 1.42
.ShapeRange.TextFrame.MarginBottom = 1.42
End With
Next 'comment
End Sub

Did you mean something like this?
I tried to run this but it said:
Method or data member not found.

Thank you for all the help in advance.

 

[Andrew Poulsom]

A comment doesn't have a Name property. What code did the macro recorder give you originally?

 

[Jim885]

MissEcxel,

I'm not sure if this is a typo when you were entering your code, however the last three lines in your code;

End Sub
Next 'comment
End Sub

should not end the macro before you go to the next comment box.

Eliminate the first "End Sub" so that your code can at least try to loop.

Other than that, try using this working example that changes the appearance of the comment boxes as a way to modify your code.

Sub ChangeCommentBox()
 Dim MyComments As Comment
 Dim LArea As Long
 For Each MyComments In ActiveSheet.Comments
 With MyComments
 .Shape.AutoShapeType = msoShapeRoundedRectangle
 .Shape.TextFrame.Characters.Font.Name = "Tahoma"
 .Shape.TextFrame.Characters.Font.Size = 8
 .Shape.TextFrame.Characters.Font.ColorIndex = 2
 .Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
 .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
 .Shape.Fill.Visible = msoTrue
 .Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
 .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
 End With
 Next MyComments
 End Sub

 

[MissEcxel]

Thank you very much for the tip!

I changed it to:
Sub ChangeCommentBoxlatesttry()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Cambria"
.Shape.TextFrame.Characters.Font.Size = 10
.Shape.Fill.Visible = msoTrue
.Shape.Fill.Solid
.Shape.Fill.ForeColor.SchemeColor = 1
.Shape.Fill.Transparency = 0#
.Shape.Line.Weight = 0.75
.Shape.Line.DashStyle = msoLineSolid
.Shape.Line.Style = msoLineSingle
.Shape.Line.Transparency = 0#
.Shape.Line.Visible = msoTrue
.Shape.Line.ForeColor.RGB = RGB(186, 179, 163)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.TextFrame.MarginLeft = 1
.Shape.TextFrame.MarginRight = 1
.Shape.TextFrame.MarginTop = 1
.Shape.TextFrame.MarginBottom = 1
End With
Next MyComments
End Sub

It works. Thank you very much for all your help!

 

[Jim885]

Thank you very much for the tip!



It works. Thank you very much for all your help!

You're welcome. Glad it worked out for you.

 

[MissEcxel]

Now i tried to add a color change but that doesn't seem to apply
Any thoughts?

Sub ChangeCommentBoxlatesttry()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Cambria"
.Shape.TextFrame.Characters.Font.Size = 10
.Shape.TextFrame.Characters.Font.Color= RGB ( 0, 0, 0)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.Solid
.Shape.Fill.ForeColor.SchemeColor = 1
.Shape.Fill.Transparency = 0#
.Shape.Line.Weight = 0.75
.Shape.Line.DashStyle = msoLineSolid
.Shape.Line.Style = msoLineSingle
.Shape.Line.Transparency = 0#
.Shape.Line.Visible = msoTrue
.Shape.Line.ForeColor.RGB = RGB(186, 179, 163)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.TextFrame.MarginLeft = 0.5
.Shape.TextFrame.MarginRight = 0.5
.Shape.TextFrame.MarginTop = 0.5
.Shape.TextFrame.MarginBottom = 0.5
End With
Next MyComments
End Sub

Thank you in advance!

 

[Jim885]

Try changing this line;

.Shape.TextFrame.Characters.Font.Color= RGB ( 0, 0, 0)

to this;

.Shape.TextFrame.Characters.Font.Color= RGB (255, 0, 0)

The font color should then become red.

Is that what you mean, or wanted to do?

 

[Jim885]

As a tip for placing your code in these threads, please enclose your code by using code tags like this;
[code>]<code]<font color="#ff0000">Place Your Code Here[/CODE>]

Just dont use the ">" that I had to include so that the format appears here.</code]