how to make global changes to comment fonts

Sorry to build your hopes up, I can't find an answer but....

A couple of questions:

What is the %zoom your viewing at? at home I have 14" monitor an I can comfortably have 100% zoom and veiw the comments fine.

When you type things into the cells what is the font size you use? Again, unless it's for a heading 10pt is MORE than enough, I often notice people use larger sizes so they can view it more clearly when they could just change the zoom.

Going beyond these bounds also causes problems for printing, Formating and (Personally speaking) adds to the untidyness of a workbook.

Having done graphic design one of the main roots of evil is people thinking that size adds to clarity, when style is the real key. (I'll get some stick for that one, but it's true (Module MAC 306, 2nd year degree module and Berger will back me up.:) )

Ian

I agree with Ian...BUT if you want to change the
size or Font then try this.

Option Explicit

'if you want to resize ALL comment boxs
'in an activesheet OR All sheets then this routine
'may help you. Resizing is @ 25% change as required.
'Or scale down.

Sub ChangeSize_Comments_SSh()
Dim cCell As Range
Dim sComment As Comment
Dim allComments As Range
Dim Sh As Worksheet
Dim Ans As Integer
Dim All As Boolean

Ans = MsgBox("Activesheet (Yes) or ALL sheets (No)", vbYesNoCancel)
If Ans = 2 Then Exit Sub

All = IIf(Ans = 7, True, False)

If Not All Then Set Sh = ActiveSheet: GoTo skipSh

For Each Sh In ActiveWorkbook.Sheets

skipSh:
On Error Resume Next
Set allComments = Sh.Range("A1").SpecialCells(xlCellTypeComments)
If allComments Is Nothing And Not All Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Ex
'Leave On Error Resume next ON to take care of Loop not set error
'when doing active sheet only.

For Each cCell In allComments
With cCell.Comment
'lock aspect to get even increase in size
.Shape.LockAspectRatio = True
'increase by 25% change as required
.Shape.Height = .Shape.Height * 1.25
End With
Next cCell
Next Sh

Ex:
Set allComments = Nothing
Set Sh = Nothing

End Sub

Sub ChangeFonts_Comments_SSh()
Dim cCell As Range
Dim sComment As Comment
Dim allComments As Range
Dim Sh As Worksheet
Dim Ans As Integer
Dim All As Boolean

Ans = MsgBox("Activesheet (Yes) or ALL sheets (No)", vbYesNoCancel)
If Ans = 2 Then Exit Sub

All = IIf(Ans = 7, True, False)

If Not All Then Set Sh = ActiveSheet: GoTo skipSh

Application.ScreenUpdating = False
For Each Sh In ActiveWorkbook.Sheets

skipSh:
On Error Resume Next
Set allComments = Sh.Range("A1").SpecialCells(xlCellTypeComments)
If allComments Is Nothing And Not All Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Ex
'Leave On Error Resume next ON to take care of Loop not set error
'when doing active sheet only.

For Each cCell In allComments
cCell.Select
cCell.Comment.Visible = True
cCell.Comment.Shape.Select True
With Selection.Font
.Bold = True
.Size = 12
End With
cCell.Comment.Visible = False
Next cCell
Next Sh
Ex:
Application.ScreenUpdating = True

Set allComments = Nothing
Set Sh = Nothing

End Sub

Ivan

Good answer Ivan..I know not the VB but.........

I STILL suggest looking into the style of the sheet before resorting to such drastic measures.
By drastic I mean, ask yourself:

Is this is something that I'm going to have to do ALL the time?

If the answers NO then fine

YES and I wouldn't.

Ian

Thank you Ivan, I will try this.
Yes, the %Zoom is kept low because there is lots of data, and you and Ian are very correct - that's why the font size of the comment boxes are difficult to read. Unfortunately, keeping the %zoom low is the only way to "shoe-horn" more info onto one screen instead of scrolling all of the time.
thanks again! Mary