Excel VBA to change Comments font on key in

[KSKWin]

How to get the Excel comment's font to be changed to the desired font (caliber) while keying in the comment box by VBA macro code.

This is code currently using, but every time i need to play.

Sub FixComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
With cmt.Shape.TextFrame.Characters.Font
.Name = "Calibri"
.Size = 8
End With
Next cmt
End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel VBA for Comments box font change while keying in event - OzGrid Free Excel/VBA Help Forum

Hi, How to change the Excel comments box Font to Caliber, while keying in the comments area (or) focusing out of the comment box with a VBA code. Below is the code I am using, but I have to run it every time for the font change to update. Sub…

forum.ozgrid.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rlv01]

while keying in the comment box

I don't think that can be done. The best you might be able to do is to incorporate some event code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call FixComments
End Sub

Sub FixComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        With cmt.Shape.TextFrame.Characters.Font
            If Not (.Name = "Calibri" And .Size = 8) Then
                .Name = "Calibri"
                .Size = 8
            End If
        End With
    Next cmt
End Sub

 

[KSKWin]

Hi,
Your solution really worked out. Awesome.

 

[KSKWin]

HI,

Pl. help in avoiding infinite looping by implementing [B]Application.EnableEvents = False[/B], Since it hangs.

Pl share the updated code.

Thanks
KSK

 

[KSKWin]

I don't think that can be done. The best you might be able to do is to incorporate some event code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call FixComments
End Sub

Sub FixComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        With cmt.Shape.TextFrame.Characters.Font
            If Not (.Name = "Calibri" And .Size = 8) Then
                .Name = "Calibri"
                .Size = 8
            End If
        End With
    Next cmt
End Sub

HI,

Pl. help in avoiding infinite looping by implementing [B]Application.EnableEvents = False[/B], Since it hangs.

Pl share the updated code.

Thanks
KSK

 

[rlv01]

HI,

Pl. help in avoiding infinite looping by implementing [B]Application.EnableEvents = False[/B], Since it hangs.

Pl share the updated code.

Thanks
KSK

There is nothing in the example code I posted that would cause infinite looping. It is an explicitly finite loop. I have no updated code to share.

 

[Joe4]

There is nothing in the example code I posted that would cause infinite looping. It is an explicitly finite loop. I have no updated code to share.

I don't see how it would create an infinite loop either, though every time a cell is selected on the sheet it will cycle through every comment on the page.
If they don't want that to happen every time, maybe they can move it to a different event, like Workbook_Open or Workbook_Close.
Then the updates might not be immediate, but would be updated whenever the workbook is opened or closed.