Modifying an excel comment created in VBA

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
    With CallingCell
        .ClearComments 'clear out the old comment if any
        .AddComment strComment 'create a new comment and insert the string from the cell
    End With
    FixComments
end Function

Sub FixComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        With cmt.Shape.TextFrame.Characters.Font
            .Name = "Calibri"
            .Size = 10
            .Bold = False
        End With
        cmt.Shape.TextFrame.AutoSize = True
    Next cmt
End Sub
rick60Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Sorry but I am getting an error at this line
Set CallingCell = Application.Caller

Object variable not set.

Why you use Application.Caller ? what is the purpose ??

Maybe you are missing a line that you did not post ??
gowlfow
0
rick60Author Commented:
Humm...  The "Dim CallingCell As Object" line should be all you need.  I've run the snippit exactly as it is posted.  Perhaps the problem lies in the references... I'm using the following:

Visual Basic for Application
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library

I'm using Excel 2010 on a Win7 system.  I shuold have included this in the original post
0
gowflowCommented:
Exactly same system and same refrences

how do you call the function and from where ?
gowflow
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rick60Author Commented:
And as to why the "Application.Caller"... it is to set the CallingCell object so I can reference the cell that has the formula in it in code... in this case, to create the comment.

For some additional info, I tried adding this line after the "With CallingCell" section just to see if the comment can be accessed in code:

MsgBox CallingCell.Comment.Text

and when executed, I get the message box with the correct text from the comment.... so I KNOW that code can 'see' the comment...  just bewhildered why it can't change the atributes while the function that created the comment is still running....
0
gowflowCommented:
actually in the application caller you don't need an object to be set just something like
abc = Application.caller

gowflow
0
rick60Author Commented:
I call the function by entering it as a formula in a cell:

in a cell, enter the formula:
=CreeateComment("This is my test comment text")

When you hit enter, it will create a comment on that cell with the text in it.
0
gowflowCommented:
ahhhaaa !!! so this function is in a module !!!
0
rick60Author Commented:
Also...  I'm creating these functions and subs in a Module of my .xlsm... not in any specific sheet.  Not sure that matters, but for clarity and completeness, thats what I'm doing.
0
rick60Author Commented:
cross answered there... but yes.
0
gowflowCommented:
ok testing ...
gowflow
0
gowflowCommented:
how do you call it otherwhyse ? like how you call it and you mention that it works.
gowlfow
0
rick60Author Commented:
The quick way is in the editor, position the cursor at the start of the sub FixComments and click 'run'.  You can also on the Developer tab in Excel, click "Macros", select "FixComments" and click Run.
0
gowflowCommented:
You are absolutely right and I am in big Ahhhhh !!!!!
Let me see a way thru

gowflow
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Works fine for me with this test code:
Sub TstAddComment()
  With ActiveSheet.Range("A1")
    .ClearComments
    With .addcomment("A").Shape.TextFrame.Characters.Font
      .Bold = True
      .Name = "Calibri"
    End With
  End With
End Sub

Open in new window

0
gowflowCommented:
I think this is your problem !!!

http://support.microsoft.com/kb/170787

gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
@Qlemo
on a Sub yes it works but we are talking about a UDF here !!!

gowflow
0
rick60Author Commented:
Wow...  after creating several variations and testing, it seems that it is true... can't be done this way.  I created a simple Sub that performed a test operation on a cell (create and modify a comment) and it worked perfectly.  Then changed it to a UDF, but ran it via the IDE, and it worked perfectly.  Then 'called' the same UDF  from a cell and it created the comment, but did not modify it....  proving a different result from the exact same code... with the only difference being how the UDF was called.  I doubt I would have come to this conclusion on my own... thanks for the help and finding the MS article that outlines the issue.
0
gowflowCommented:
Yes and it tough me something I sure didn't know. Tks for asking !!! we never cease to learn.
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.