Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Code to Edit Cell Comment - Not Using Sendkeys

Posted on 2015-01-13
6
Medium Priority
?
326 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
[X]
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
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
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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

704 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