How to open a comment for user editing from VBA (XL 2000).

Hello, Experts,

I am trying to create a Macro (in XL 2000) that will create a customized comment and open it for the user to modify.  The first part is easy:

    ActiveCell.AddComment ("Customized Text")

However opening the resulting comment seems to pose a problem.  Neither the Comment object nor its associated Shape object appear to offer any appropriate methods.  Attempting to use:


results in the error message:

    Run-time error '-2147467259 (80004005)':
    Method 'Select' of object 'Shape' failed

As I read the documentation, the command:

    Application.CommandBars(1).Controls("&Insert").Controls("&Edit Comment").Execute

should do just what I want, but this seems totally ineffective. That is, nothing happens at all.  Not even an error message.   (The corresponding menu command can be invoked using SendKeys "%iE~", but of course SendKeys is inherently unreliable/risky and I won't use it in any "real" application.)

Can anyone suggest how I can use VBA to open a Comment for user editing?

LVL 12
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:

IMO, if you really want to open the comment for editing (i.e. mimic the built-in behaviour), then SendKeys is your only option. You can select the shape if you make it visible first, but then it will remain visible after the user has finished editing it (unlike the normal behaviour)

SiddharthRoutConnect With a Mentor Commented:
Use a userform and a textbox. In the initialize event extract the comment text and place it in the textbox and let the user edit it. From there you can then directly save the comment. Let me know if you want an example.

zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Here is some sample code to get you started.

The macro below adds a blank comment to the active cell without any text or formatting. This macro is used as an alternative to Excel's default comment insertion behavior which always adds the user name in bold to the beginning of every new comment. The code is compatible with all Excel versions.

[Begin Code Segment]

Public Sub InsertCustomComment()

' Insert a comment in the active cell with no formatting or text. This macro is
' used to bypass Excel's default comment insertion behavior which inserts the
' user name in bold in every new comment.

   If ActiveCell Is Nothing Then
      Exit Sub
   End If
   ' Add the comment
   If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
   ' Modify the text formatting
   With ActiveCell.Comment.Shape.TextFrame.Characters
      ' Set the font name
      '.Font.Name = "Courier"
      ' Set the font size
      '.Font.Size = 15
      ' Set the font bold
      '.Font.Bold = True
      ' Set the font italic
      '.Font.Italic = True
      ' Set the font color
      '.Font.ColorIndex = 3
   End With
   ' Modify the shape size and margin properties
   With ActiveCell.Comment.Shape.OLEFormat.Object
      ' Set the horizontal position
      '.HorizontalAlignment = xlLeft ' xlLeft, xlCenter, xlRight
      ' Set the vertical position
      '.VerticalAlignment = xlTop ' xlTop, xlCenter, xlBottom
      ' Set the orientation
      '.Orientation = xlVertical ' xlHorizontal, xlDownward, xlUpward, xlVertical
      ' Turn auto sizing on
      '.AutoSize = True
   End With
   ' Modify the shape color and line properties
   With ActiveCell.Comment.Shape.OLEFormat.Object.ShapeRange
      '.Fill.ForeColor.SchemeColor = 3
      '.Fill.Transparency = 0.5 ' 0 (opaque) to 1 (transparent)
      ' Turn the frame shadow off
      '.Shadow.Visible = msoFalse
      ' Set the frame color
      '.Line.ForeColor.SchemeColor = 12
      ' Hide the frame
      '.Line.Visible = msoFalse
   End With
   ' Open the comment for editing
   Application.SendKeys "+{F2}Enter text+^{LEFT}+^{LEFT}"
End Sub

[End Code Segment]

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

omegaomega: Further to my post above, here is a sample attached. Run the Userform1


Code Used

Dim bHasComment As Boolean
Private Sub CommandButton1_Click()
    Range(RefEdit1.Value).Comment.Text Text:=TextBox1.Value
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    On Error Resume Next
    bHasComment = _
    Range(RefEdit1.Value).Comment.Parent.Address = Range(RefEdit1.Value).Address
    On Error GoTo 0
    If bHasComment Then
        TextBox1.Text = Range(RefEdit1.Value).Comment.Text
    End If
End Sub

Open in new window

Zorvek: That a nice piece of code. :) Added it to my code bank. ;-)
omegaomegaDeveloperAuthor Commented:
Hi, Sid, Kevin & Rory,

Thanks for your helpful suggestions.  Rory's note made me realize that I must have been working too late since it hadn't occurred me that the error I was seeing was due to trying to select a hidden object.

I suspect that Rory & Kevin may be correct, that there is no solution that doesn't involve SendKeys.  

Sid, your suggestion to use a UserForm doesn't give me quite what I need because it doesn't allow for formatting the text or the comment box.  But it did get me thinking about using a TextBox (or Callout) to emulate the Comment box.  The idea was to copy the properties from the TextBox's Shape object back to the Comment's Shape object when the user again selected the Worksheet.  

This seemed to work quite well ... except for the same "gotcha" that prevents me from using Rory's last suggestion.  If the user happens to "select" the same cell in which the Comment has been created, there is no SheetSelectionChange event raised, so no opportunity to hide the Comment box that had been made visible (or the Textbox/Callout emulating the Comment box).

I really don't want to use SendKeys, but am willing to accept that as the answer.   (Sigh).  Still I wonder why

    Application.CommandBars(1).Controls("&Insert").Controls("&Edit Comment").Execute

fails to work.  Other commands such as:


can be  invoked successfully.

Thanks again for your suggestions and advice.


omegaomegaDeveloperAuthor Commented:
Thanks for all input.  Rory I guess "(NFP)" might mean "not for points", but your answer was very enlightening, and I am much obliged.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.