• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to change the text in all comment boxes

M

MalR

Member
Hi guys. My CPU failed, did several stop/starts. I use hundreds of comment boxes in an excel spreadsheet. It changed the text in all the comment boxes to a strange font. I understand comments can be formatted individually in Excel but to change the default setting it must be done in Windows. In Win 7, I used to be able to access the setting in Windows through Advanced appearance settings/ToolTip. In Win10 this feature has gone.
Does anyone know how to access the Windows control in Win10 to enable me to set a universal font size for my comments please?
 
Code: 
Sub Reset_Comments()

Dim c As Comment
For Each c In ActiveSheet.Comments
  With c.Shape.TextFrame.Characters.Font
  .ColorIndex = 1 'Font Color
  .Size = 10 'Font Size
  .Name = "Calibri" 'Font Type
  End With
Next c

End Sub
 
Thanks Hui. Very helpful. It seems the font in my new comment boxes has defaulted back to the original text today. Must have been a MS update overnight that repaired it I guess. Your macro will be helpful to fix the existing ones which now remain with that problem.
Appreciate your help
MalR
 
Are you sure you don't have any VBA code that is altering them when the file opens?
 
It all occurred after my CPU had a hernia Hui. It stopped and started several times and blue screened. I dismantled it and blew out the fluff. It has worked perfectly since. Strange coincidence. I use comments extensively and I do have a macro that sets the size of the comments. I need a box of a certain size. I can submit it if you need. But it has no code to affect the font. Also strange is for 3 or 4 days my new comments had the same problem font. It is a regular size but has a Chinese font name. Then from today my new comments are back to normal when I open them. I assumed MS did an update and it got fixed but my existing comments are still affected. If you follow that through then logically why did not my existing comments get fixed when the new ones did? They both (existing and new) all went out at once. I do not know. All I am sure of is my new comment boxes are back to normal and my existing ones are compromised. I have not loaded anything or done any new formulas to cause this. This is why I blamed the CPU issue.
Always interested to hear any suggestions you might have and thanks for the reply.
 
No idea

But glad it is behaving as planned

If you want to change the Text size for all comments on all worksheets in a workbook, try this code:

Code: 
Sub ResetCommentStyle()

Dim sh As Worksheet
Dim c As Comment
Dim i As Integer

'Step through each Worksheet
For Each sh In Worksheets
'Step through each Comment
For Each c In sh.Comments
With c.Shape.TextFrame.Characters.Font
.ColorIndex = 1 'Font Color
.Size = 10 'Font Size
.Name = "Calibri" 'Font Type
End With
i = i + 1
Next c
Next sh

msgtxt = "Updated " + CStr(i) + " comments."
msgtxt = msgtxt + Chr(10) 
msgtxt = msgtxt + "On " + CStr(Worksheets.Count) + " workshets"
MsgBox msgtxt, vbOKOnly, "Comment Update Complete"

End Sub
 
You must log in or register to reply here.
Back
Top