We help IT Professionals succeed at work.
Get Started

Modifying an excel comment created in VBA

rick60
rick60 asked
on
167 Views
Last Modified: 2016-02-10
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
Partner
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 18 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE