rescuing comments section from the 80's trap

[msword]

Hi. I work with comments everyday and disability to
1. set default font/size
2. non-changable positioning outside the viewport
ruins everything. I am even thinking to switch to another piece of software just because of this. After some googling I discovered that comments section code base not changed since 80's, thus fix of the mentioned bugs are not expected in the next 5-10 years. As a guy that never wrote a line of code, I wonder is there any chance to fix this using VBA or something?
I tried to run this code:

Sub FormatAllComments()
'Updateby20140509
Dim xWs As Worksheet
Dim xComment As Comment
For Each xWs In Application.ActiveWorkbook.Worksheets
 For Each xComment In xWs.Comments
 With xComment.Shape.TextFrame.Characters.Font
 .Name = "Times New Roman"
 .Size = 33
 End With
 Next
Next
End Sub

but getting the error message:

I suppose that mentiong that I am sitting on the macos version of Excel is also important.
Thanks.

 

[Trebor76]

Hi there,

Based on Damon's solution from this thread the following should do the job:

Option Explicit
Sub Macro2()

    Dim rngcell As Range
    Dim xWs As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each xWs In ThisWorkbook.Worksheets
        For Each rngcell In xWs.UsedRange
            If Not rngcell.Comment Is Nothing Then
                With rngcell.Comment.Shape.TextFrame.Characters.Font
                    .Size = 33
                    .Name = "Times New Roman"
                End With
            End If
        Next rngcell
    Next xWs
    
    Application.ScreenUpdating = False

End Sub

comments section code base not changed since 80's

Considering VBA was first released for Excel in 1993 I'm not sure about this

Regards,

Robert

 

[msword]

Same error as shown on the screenshot beyond. Do I paste it in the right direction(Tools > Macro > Visual Basic Editor > This Workbook > RMC > Insert > Module > "Run Sub/UserForm" button)?

 

[Trebor76]

Yes that's right and then you'd run Macro2 whenever you need.

Strange as it works for me? Is the tab protected?