We help IT Professionals succeed at work.

Modifying an excel comment created in VBA

rick60
rick60 asked
on
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
Comment
Watch Question

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

Author

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
Exactly same system and same refrences

how do you call the function and from where ?
gowflow

Author

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....
actually in the application caller you don't need an object to be set just something like
abc = Application.caller

gowflow

Author

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.
ahhhaaa !!! so this function is in a module !!!

Author

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.

Author

Commented:
cross answered there... but yes.
ok testing ...
gowflow
how do you call it otherwhyse ? like how you call it and you mention that it works.
gowlfow

Author

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.
You are absolutely right and I am in big Ahhhhh !!!!!
Let me see a way thru

gowflow
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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

I think this is your problem !!!

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

gowflow
@Qlemo
on a Sub yes it works but we are talking about a UDF here !!!

gowflow

Author

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.
Yes and it tough me something I sure didn't know. Tks for asking !!! we never cease to learn.
gowflow