Inserting a comment in a cell after typing a value

Hi everybody:

How I can automatically open the comment box for each cell once I type a value?

Thanks!

Miguel
philosopher76Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can press Shift+f2..This will either open a comment if a comment is their or you can enter a new comment basis of your requirement because this will insert a comment if it's not their..
0
frankhelkCommented:
@Saurabh Singh Teotia: That's a manual action ... I presume that philosopher76 intended to open that automatically whenever a value has been entered.

If you put this into the VBA code of the respective table, it should be close to the desired result:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim text As String
    text = InputBox("Comment")
    
    For Each cell In Target.Cells
        cell.AddComment (text)
    Next
    
End Sub

Open in new window

Please bear in mind that this code outlines the mere concept only - for smooth working experience there should be some extensions, i.e. for
limiting the action to the desired cells,
preventing exceptions if the entered comment is empty
switching the functionality on/off by the user
etc., etc., etc. ...
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frankhelkCommented:
Second attempt:

If you want to use the native comment editor, you could just stuff the appropriate keystroke into the keyboard buffer:
Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Select
    SendKeys ("+{F2}")
End Sub

Open in new window


Same restrictons as in my previous post apply. The Target.Select command is needed because the selected cell changes usually before the keyboard queue is accessed - therefore that code needs some fine tuning, too.

The needed keystroke descriptor could be found in this MSDN article, while the keystroke itself seems to be locally different (SHIFT-F2 in German versions, SHIFT-F11 where Saurabh Singh Teotia lives, sth. else elsewhere .... refer to Excel help about keyboard shortcuts about the correct one). There might be a way to activate the comment editor in a more elegant way, but I havn't found any ... that would need much more investigation to find (if that is possible vith VBA).
0
philosopher76Author Commented:
Thanks!

I had to delete the second line of the code and now it works.

As I am new VBA in Excel, I do not understand  exactly why the second line is not necessary but I will be working on it.
0
philosopher76Author Commented:
I had to delete Dim ......line to run the  code perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.