Solved

VBA Code to Edit Cell Comment - Not Using Sendkeys

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

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

Title # Comments Views Activity
Input Macro 8 22
Tags from access to excel 3 24
Formula or Macro to determine variance 17 73
Excel 2010 Text Formatting placing a hyphen in front of text 3 20
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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.

948 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

21 Experts available now in Live!

Get 1:1 Help Now