G'day there One & All,

I have a workbook where my users copy hyperlinks across from one sheet
to another. That works fine, as does the code that then builds a comment
from various cells in the first sheet to give a bit of a description of
what's in the hyperlinked document. That all works fine. What is driving
me nuts, however is that I can't format the comments from within my
calling procedure.

After hours of trial and error failed dismally, a web search found this
little snippet:

Public Sub ChangeCommentFormat()
For Each c In ActiveSheet.Comments
With c.Shape.TextFrame.Characters.Font
.Name = "Tahoma"
.Size = 10
.Bold = False
End With
c.Shape.TextFrame.AutoSize = True
Next
End Sub

It works well, but only if I run it with F5 from within the module.
I've tried calling it from the end of the procedure that builds and adds
my comments. I've called if from the worksheet_change event that calls
the comment building routine. I've tried adapting it to work on an
individual comment called, once again, from within the comment builder.
Nothing works and my comments are then written in bold font with too
small a shape to fit the text in. The above code works quickly and gives
the exact result, but I don't expect my clients to run it manually when
it should just be done each time a comment is inserted from my code.

Does anyone know why it works in this manner? Or does anyone have a
clue as to how to format comments on the fly? To date I've spent over 6
hours trying various methods and am still trying to get it to work.

Hoping someone has more clues than I do.
Thanks for listening
Ken McLennan
Qld Rosewood