Auto Comment??

[oh_b1]

Is there a way to get an automatic insert comment text box to pop up if a partcular input is entered into a cell?? For example if "NO" is entered into any cell in the range I5 to TG50 I would like the insert comment text box to atomatically appear for that cell so a comment can be recorded. Is this or something that would accomplish something similar possible?

Thanks, I appreciate any/all help with this.
Take care, Rick.

 

[baitmaster]

Option compare text ' makes module non-case sensitive
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
For Each cl In Target
    If cl = "No" Then
        cl.AddComment "add text here"
    End If
Next cl
End Sub

You would need additional error handling in case comment already exists. This code works on ranges of cells as well as individual cells

 

[oh_b1]

Thanks for the help! This is almost exactly what I was looking for. It is creating a comment for the correct cell once a "NO" is entered although it is not open for edit, you have to go back into comment and edit it to add text, maybe I did something wrong? Is there a way to have it open in edit mode when it is created, so you can type immediately into it without having to open it back up in edit mode??

Take care, Rick.

 

[oh_b1]

Option compare text ' makes module non-case sensitive
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
For Each cl In Target
    If cl = "No" Then
        cl.AddComment "add text here"
    End If
Next cl
End Sub

You would need additional error handling in case comment already exists. This code works on ranges of cells as well as individual cells

Thanks for the help! This is almost exactly what I was looking for. It is creating a comment for the correct cell once a "NO" is entered although it is not open for edit, you have to go back into comment and edit it to add text, maybe I did something wrong? Is there a way to have it open in edit mode when it is created, so you can type immediately into it without having to open it back up in edit mode??

Take care, Rick.

 

[baitmaster]

I don't think this is possible, especially in the case where multiple cells are changed

You could use an inputbox to request the user enter data, which can then be passed to the comment in place of the default text I provided. You would need to decide whether to ask the question for each cell, or the entire changed range, and structure your code accordingly

 

[oh_b1]

Thanks for your help with this!! The users would be inputting one cell at a time with either a "yes" or "no" from a validation drop down list. For all inputs with "no" entered I need a comment as to why. Each cell would only ever receive one reply and one reply only. Each column representing a day in the calendar year. I am not very familiar with writting code and I am not sure what the inputbox is and/or how to set that up as you have suggested. If you could explain a bit more and/or if the information above helped with a possible alternative solution.....any/all input on this is greatly appreciated. Even not permitting the user to move on until the comment is created/entered would work, basically forcing a comment.

Take care, Rick.

 

[baitmaster]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strExplanation As String
Application.EnableEvents = False
 
If Target.Cells.Count > 1 Then
    MsgBox "you are only allowed to change one cell at a time", vbCritical
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
End If
 
If Target = "No" Then
furtherDetails:
    strExplanation = InputBox("please give an explanation", "further information required")
    If strExplanation = "" Then GoTo furtherDetails
    Target.AddComment strExplanation
End If
    
Application.EnableEvents = True
End Sub

This approach is not ideal if you need to prevent the Users doing certain things. I've adde a simple test to ensure they don't try and cheat by entering multiple details at once, and you can see some of the complexity this adds. DO NOT underestimate the ability of even the most educated user to simply screw everything up either deliberately or accidentally!

Enableevents was switched off to stop the worksheet_change triggering undo, with undo in turn triggering worksheet_change, with.... etc etc. Enableevents was switched on again at the end of the routine, otherwise these automatic macros won't run again during this session of Excel. If your code breaks before ending, you will need to switch events back on manually, via the immediate window of the VB Editor

I've added the named code location "furtherDetails", in order to force the user to enter something

If the user goes back and changes something, the comment will remain incorrectly. If they then re-enter the value "no", you will encounter an error because the comment already exists so you cannot add a new one - you should therefore consider automatically deleting any existing comments if the answer is no longer "no"

If trying to collect data in a specific order, with very limited answers, you may need to consider something more complex than this approach, for various reasons. Welcome to the wonderful world of VBA, where nothing is as simple as it first seems...

 

[oh_b1]

That worked perfect for what I needed, thanks for the help it is very appreciated!!