Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Code to Edit Cell Comment - Not Using Sendkeys

Posted on 2015-01-13
6
Medium Priority
?
350 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 49

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 49

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
Technology Partners: 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

885 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