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

Posted on 2011-03-03
Last Modified: 2013-11-05
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?

Question by:omegaomega
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 30

Assisted Solution

SiddharthRout earned 150 total points
ID: 35032989
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.

LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 150 total points
ID: 35032995
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]

LVL 30

Expert Comment

ID: 35033058
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. ;-)
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

LVL 85

Accepted Solution

Rory Archibald earned 200 total points
ID: 35034825

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)

LVL 12

Author Comment

ID: 35038830
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.


LVL 12

Author Closing Comment

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


Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question