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.

 

[MissEcxel]

Dear Jim885,

I tried;

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

But no matter what i try it doesn't change the color of the text in the comments.

I tried:

.Shape.TextFrame.Characters.Font.ThemeColor = xlThemeColorAccent3

But somehow it won't change.

Even if i use the original code;

[COLOR=#333333]Sub ChangeCommentBox()[/COLOR]
 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 
[COLOR=#333333] End Sub
[/COLOR]

It won't change to white.

 

[jimrward]

this will change comments to RED

Sub MoveCellCommentsToRed()

Dim rgcell As Range
Dim rgcomments As Range

Set rgcomments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

For Each rgcell In rgcomments
    rgcell.Comment.Shape.TextFrame.Characters.Font.ColorIndex = 3
Next rgcell
End Sub

 

[MissEcxel]

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.
Trying to find a solution for that.

I tried;

Sub SetCellComments()
Dim wsHeet As Worksheet
Dim cCom As Comment
    
    For Each wsHeet In Worksheets
        For Each cCom In wsHeet.Comments
            With cCom.Shape
                .Locked = True
                .ControlFormat.LockedText = False
            End With
        Next cCom
    Next wsHeet
End Sub

But;

.ControlFormat.LockedText = False

turns yellow in debug mode it tels me the object doesn't support this action it would suck to manually do it to about 2000 comments.

 

[Andrew Poulsom]

Try removing .ControlFormat.

 

[MissEcxel]

Hi,
I tried;

Sub SetCellComments()Dim wsHeet As Worksheet
Dim cCom As Comment
    
    For Each wsHeet In Worksheets
        For Each cCom In wsHeet.Comments
            With cCom.Shape
                .Locked = True
                .LockedText = False
            End With
        Next cCom
    Next wsHeet
End Sub

It warns me Method or data member not found.

 

[MissEcxel]

/Users/Admin/Desktop/Screen Shot 2014-11-11 at 16.27.26.png

 

[Andrew Poulsom]

Your original code works for me in Excel 2010. What version of Excel are you using?

Sub SetCellComments()
    Dim wsHeet As Worksheet
    Dim cCom As Comment
    For Each wsHeet In Worksheets
        For Each cCom In wsHeet.Comments
            With cCom.Shape
                .Locked = True
                .ControlFormat.LockedText = False
            End With
        Next cCom
    Next wsHeet
End Sub

 

[MissEcxel]

I'm using Excel for mac 2010.
VBA 14.2.3

I unlocked the shape successfully with the code below now im trying to adjust it to unlock the textfields also.

Function CommentProtection(wksht As Excel.Worksheet, _    Optional lockComments As Boolean = False)
 
Dim cmt As Excel.Comment
 
  For Each cmt In wksht.Comments
    cmt.Shape.Locked = lockComments
  Next cmt
 
End Function


Sub TestCmtProtection()
 
Dim wksht As Excel.Worksheet
Dim wb As Excel.Workbook
 
Set wb = ActiveWorkbook
 
  For Each wksht In wb.Worksheets
    Call CommentProtection(wksht)
  Next wksht
 
End Sub

 

[Andrew Poulsom]

I don't have Excel for Mac, so I can't help sorry.

 

[MissEcxel]

Thank you i'm posting the question to the Microsoft forum i'll update the post when i found an answer.