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
Solved

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

Posted on 2011-03-03
6
350 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
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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