How do you update all existing Notes Previously known as comments and put something in the text field using VBA Code

[iresolver]

I have over 50 blank Notes on a sheet in Excel I need to be able to update and add something to all of them and they are in merged cells. What is the VBA code to do this I can't figure it out?
Any help would be appreciated.
thanks, guys!

My VBA Code

Sub My_FIX_Notes()

' This Macro will change all Note Comments to Transparent Blue Background  & Bold White Text
CommentCount = 0
Dim MyComments As Comment
Dim LArea As Long
Dim fixed As Boolean
fixed = False

For Each MyComments In ActiveSheet.Comments
With MyComments
' .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Arial"
.Shape.TextFrame.Characters.Font.Size = 12
.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.TextFrame.Characters.Font.Bold = True
.Shape.Fill.BackColor.RGB = RGB(58, 82, 184)

' This fills background color of the Note and sets the transarancey to 0.04
.Shape.Fill.ForeColor.RGB = RGB(85, 85, 110)
.Shape.Fill.Transparency = 0.04

'This Sets the With & Height of each Note Comment Hotkey
MyComments.Shape.Width = 200
MyComments.Shape.Height = 40

CommentCount = CommentCount + 1

End With
Next 'comment


If CommentCount > 0 Then
'MsgBox ("A total of " & CommentCount & " comments in worksheet '" & MySheet.Name & "' of workbook '" & MyWorkbook.Name & "'" & Chr(13) & "were repositioned and resized.")

MsgBox ("A Total Of " & CommentCount & "" & "Were change")

fixed = True
End If

'Next
' Next MyWorkbook
'thisfile.Activate


If fixed = False Then
MsgBox ("No comments were detected.")
End If

On Error GoTo 0

Exit Sub
End Sub

 

[sijpie]

The new comments belong to on object called CommentsThreaded. These have only a limited set of properties you can modify.

In the VBA editor press F2 to get to the library. The search or scroll down to CommentThreaded and you can see the properties and methods it has.

A good way of finding out stuff is to record a macro in Excel, do waht you want to do and then look at the code that Excel produced.

So your code would be something like:

Option Explicit

Sub My_FIX_Notes()

    ' This Macro will change all Note Comments to Transparent Blue Background  & Bold White Text
    Dim MyComments As CommentThreaded
    Dim LArea As Long
    Dim fixed As Boolean
    Dim CommentCount As Integer
    
    fixed = False
    CommentCount = 0
    
    For Each MyComments In ActiveSheet.CommentsThreaded
        With MyComments
            'set the text (dummy example)
            .Text Text:=.Text & " " & CommentCount
        
        '>>> You can't set colour or size properties of the threaded comments
        
''            ' .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
''            .Shape.AutoShapeType = msoShapeRoundedRectangle
''            .Shape.TextFrame.Characters.Font.Name = "Arial"
''            .Shape.TextFrame.Characters.Font.Size = 12
''            .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.TextFrame.Characters.Font.Bold = True
''            .Shape.Fill.BackColor.RGB = RGB(58, 82, 184)
''
''            ' This fills background color of the Note and sets the transarancey to 0.04
''            .Shape.Fill.ForeColor.RGB = RGB(85, 85, 110)
''            .Shape.Fill.Transparency = 0.04
''
''            'This Sets the With & Height of each Note Comment Hotkey
''            MyComments.Shape.Width = 200
''            MyComments.Shape.Height = 40
            
            CommentCount = CommentCount + 1
        
        End With
    Next 'comment
    
    
    If CommentCount > 0 Then
        'MsgBox ("A total of " & CommentCount & " comments in worksheet '" & MySheet.Name & "' of workbook '" & MyWorkbook.Name & "'" & Chr(13) & "were repositioned and resized.")
        
        MsgBox ("A Total Of " & CommentCount & "" & " comments were changed")
        
        fixed = True
    End If
    
    'Next
    ' Next MyWorkbook
    'thisfile.Activate
    
    
    If fixed = False Then
        MsgBox ("No comments were detected.")
    End If
    
    On Error GoTo 0
    
    Exit Sub
End Sub