Open comment box when cell is edited

[Emzibibilybob]

Hi. Very very new to VBA and I'm slowly getting it to do what I need, mainly with the help of you lovely people on Mrexcel.

Anyway, trying to create a spreadsheet, whereby when text is entered in a box in column K-P, it automatically opens a comment box to be completed linked to that cell. Once the comment is completed and another cell is selected, the comment can then close.

Any help you can provide would be greatly appreciated.

 

[My Aswer Is This]

I'm assuming your saying any time you enter any value in columns K To P
You want a comment entered. And the value you enter in the cell will now be the Text in the comment.

So if you enter "Alpha" in Range "K14" the Text "Alpha" will now be in a Comment attached to Range "K14" and the comment will not be visible unless you hover over range "K14"

And now comments have been greatly changed in the last several versions of Excel.
So my script will work in Excel 2013 but not sure about newer versions of Excel.

If this is not what you want be explain more.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

'Modified  12/11/2020  10:41:25 AM  EST

If Target.Column > 10 And Target.Column < 17 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

With Target
    .AddComment
    .Comment.Text Text:=Target.Value
    .Comment.Visible = False
End With
End If
End Sub

 

[Zot]

This is the way I understand what you wanted.

If you key in any text in a cell within column K and P, you will get comment box popped up.
After typing comment in the comment box, you click another cell and the comment in previous cell will close
The comment box will pop up once you enter something in current cell, waiting for you to put another comment

Is this what you meant?

 

[Emzibibilybob]

Hi

Thanks for the reply both of you. It tried this code and the problem I have is this opens up a note in the cell and I need it to be a comment that can be added to. I will try and explain a little more to put my problem into context.

I have a sheet and in column K to P there are a number of tasks that need completing.
I will be asking the member of staff who has completed the task to input their initials into the box with the corresponding date and task, and I would then like a comment box to open that would allow the individual to enter additional text pertaining to that task. For example, when John Smith opens the building he would put JS in column K and this would then open a comment box against that cell that would allow him to also enter additional info if say the alarm needed a reset or there was a broken lock. Once the user then clicks into another box to initial another task, the 1st comment box is closed and when text is entered in the other cell another box would appear.

I need comment boxes instead of notes as it tracks the dates times and users entering the information.

I hope this makes a little sense.