Solved

VBA Code to Edit Cell Comment - Not Using Sendkeys

Posted on 2015-01-13
6
268 Views
Last Modified: 2015-01-14
Hello Experts,

I have the following code, which is a variation of insert comments in Excel, where one of the added features is that in addition to including the Username, it also includes the date.  The code works perfectly, where a comment is inserted onto a cell, and if a comment exists, it does not override the existing comment, but instead, the "new comment" is inserted right after the existing comment.

Sub CommentDateTimeAdd()
     
    Dim objComment As Comment
    Dim strHeader, strDate As String
     
    strHeader = Application.UserName & ":"
    strDate = "(mm/dd/yy)"
    Set objComment = ActiveCell.Comment
     
    If objComment Is Nothing Then
        Set objComment = ActiveCell.AddComment
        objComment.Text Text:=strHeader & Chr(10) _
        & Format(Now, strDate) & Chr(10)
    Else
        objComment.Text Text:=objComment.Text & Chr(10) _
        & Chr(10) _
        & strHeader & Chr(10) _
        & Format(Now, strDate) & Chr(10)
    End If
        
    With objComment.Shape.TextFrame
        .Characters.Font.Bold = False
        .Characters(1, Len(strHeader + strDate)).Font.Bold = True
    End With
    

End Sub

Open in new window


As the last step, I would like the code to open the comment and allow the user to edit it.  I tried using the
SendKeys "+{F2}" at the end of the code, which does work, but the SendKeys function toggles the NumLock On/Off and I would like to avoid this if possible.  What would be another way to open the comment for edit?

Any help with this will be greatly appreciated.

Thank you,


Sura
0
Comment
Question by:SuraDalbin
6 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40547775
Assuming the cell is active, this will do it.

ActiveCell.Comment.Visible = True
0
 

Author Comment

by:SuraDalbin
ID: 40547787
Hello Martin Liss,

Thank you for your help.  This definitely works, but I was wondering if it'd be possible to somehow hide the comment afterwards.  See, I wouldn't want the comment to be visible necessarily because it'll make the worksheet a bit too crowded, since it's possible that I'll be inserting multiple comments in any give sheet.

Thank you again.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40547807
You could do this which assumes the comment is in A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Range("A1").Comment.Visible = False

End Sub

Open in new window

0
Independent Software Vendors: 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 18

Expert Comment

by:krishnakrkc
ID: 40548275
May be you can use a Inputbox to get the comment from user.

    strComment = Application.InputBox("Enter the comment:", "New Comment", Type:=2)
    If Not strComment = "False" Then
        'your code to add comment...

Open in new window


Edit: Inputbox only allows 255 characters.

Kris
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40548593
You could try this:

Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
                                      ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Const KEYEVENTF_KEYUP = &H2
Private Const vbKeyShift = &H10
Const VK_F2 = 113

Public Sub SendShiftF2()
   keybd_event vbKeyShift, 0, 0, 0
   keybd_event VK_F2, 0, 0, 0
   keybd_event VK_F2, 0, KEYEVENTF_KEYUP, 0
   keybd_event vbKeyShift, 0, KEYEVENTF_KEYUP, 0
End Sub

Open in new window

0
 

Author Closing Comment

by:SuraDalbin
ID: 40550568
Thank you Rory, this works like a Charm.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

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;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

733 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