Solved

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

Posted on 2011-03-03
6
354 Views
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:

    ActiveCell.Comment.Shape.Select

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?

Thanks,
Randy
0
Comment
Question by:omegaomega
[X]
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
6 Comments
 
LVL 30

Assisted Solution

by:SiddharthRout
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.

Sid
0
 
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
      Beep
      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]

Kevin
0
 
LVL 30

Expert Comment

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

Sid

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. ;-)
Comment.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 200 total points
ID: 35034825
(NFP)

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)

Regards,
Rory
0
 
LVL 12

Author Comment

by:omegaomega
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:

    Application.CommandBars(1).Controls("&Edit").Controls("&Paste").Execute

can be  invoked successfully.

Thanks again for your suggestions and advice.

Cheers,
Randy


0
 
LVL 12

Author Closing Comment

by:omegaomega
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.

Cheers,
Randy
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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