I have a problem where I am creating a comment in excel and then trying to modify the comment's attributes (font, point size, autosize, etc.). My code successfully clears any existing comment in the cell and creates a new one with the proper content but subsequent code that changes the attributes simply does not affect the actual comment. It's very strange. If I replicate the code that modifies the attributes into a stand-alone function, and runn it AFTER the procedure rus that created the comment has ended, the attributes will be changed. It's as if the vba code to change the atributes has no effect if it is run, or even called, from the same function that created the comment. Here is the code for the function and sub to 'fix' the comments. If I run the "FixComments" AFTER the "CreateComment" function has completed, it works fine. If I include the code in the "CreateComments" function, or call it from that function (as it is in the snippet below) then it does not work. No errors... just no results. I've tried adding a delay after creating the comment, no help. I've tried doing a "DoEvents" after creating the comment... no help. Any ideas?
'in a cell, enter the formula:[ =CreeateComment("This is my test comment text")]
Function CreateComment(strComment As String)
Dim CallingCell As Object
Set CallingCell = Application.Caller'this is the cell that
.ClearComments 'clear out the old comment if any
.AddComment strComment 'create a new comment and insert the string from the cell
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
.Name = "Calibri"
.Size = 10
.Bold = False
cmt.Shape.TextFrame.AutoSize = True