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.

 

[Jim885]

Thank you for the help!
That actually works on my new file.
Found the problem with the old one my excel automatically locks the text and object on the comment shape.

New file, old file?
Perhaps this is the problem?
Are these two files different versions of Excel (2003 vs. 2010)?

I seem to remember that the code to modify comment boxes was sligltly different between 2003 and 2010 versions of Excel, but that's about all I can remember, if my memory is correct.
Try saving the old file in a new version of Excel using SaveAs.
That would be the only suggestion I have.

The codes provided by myself and the others work for me (I'm also using a PC with Windows 7 and Excel 2010).
I have no Mac experience. Much of VBA coding is no different for a Mac machine than it is for a Microsoft machine. But this situation might be different.

 

[Jim885]

You might also find this bit of information helpful (especially post # 5);
http://www.mrexcel.com/forum/excel-questions/57296-visual-basic-applications-addcomment.html

Also see;
http://support.microsoft.com/kb/213680