Solved

VBA Code to Edit Cell Comment - Not Using Sendkeys

Posted on 2015-01-13
6
257 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 46

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 46

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
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.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

807 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