Solved

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

Posted on 2011-03-03
6
344 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now