Notes (aka Comments) VBA to change existing comments to same font size

[Ironman]

Hi

I have a number of notes (Microsoft 365 term for Comments) that contain different font sizes.

Is it possible to change all of these to font size 7 using VBA?

I have tried inserting

.Font.Size = 7

in some existing code as below, that resizes and relocates notes boxes and it errored 438 "Object doesn't support this property or method".

Sub AutosizeAndRelocateComments()
Application.ScreenUpdating = False
Dim x As Range, y As Long
For Each x In Cells.SpecialCells(1)
Select Case True
Case Len(x.NoteText) <> 0
With x.Comment
.Font.Size = 7
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 250 Then
y = .Shape.Width * .Shape.Height
.Shape.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft
.Shape.ScaleWidth 1#, msoFalse, msoScaleFromTopLeft
End If
End With
End Select
Next x

Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
   cmt.Shape.Top = cmt.Parent.Top + 5
   cmt.Shape.Left = _
      cmt.Parent.Offset(0, 1).Left + 5
Next
Application.ScreenUpdating = True
End Sub

Thanks!

 

[Ironman]

I found some code online that does the trick and added it below the code above

Dim Cell As Range  ' reduces font size to 7 (can't be done by default without changing font size for cells)
For Each Cell In Cells.SpecialCells(1)
If Not Cell.Comment Is Nothing Then
With Cell.Comment.Shape.TextFrame.Characters.Font
.Size = 7
End With
End If
Next Cell
End Sub

I tried inserting it into the above but it kept erroring. If somebody is able to do this, that would be appreciated, thank you.

 

[Fluff]

In your original code change

.Font.Size = 7

to

.Shape.TextFrame.Characters.Font.Size = 7

 

[Ironman]

Ahhh that's great Fluff, thanks ever so much, as ever.

 

[Fluff]

You're welcome & thanks for the feedback.