Insert a comment based on the value of a cell

Greetings,

I have data validation and conditional formatting set for my sheets. However, I cannot figure out something.

I want a "comment" box (just as if I would click on "add comment") to pop up automatically if the user enters a certain value in a cell.

For example, if cell C4="1" then no further action is required. If cell C4=anything other than "1", then the user would be required to enter a comment. I would also like the comment to autopopulate with the users login id.

I will attach the book I am working on for reference, but I do not have any code written for this as I could not figure out where to start.

Any help would be appreciated!

hi
try this one HTH

Please Login or Register  to view this content.

Place Both these codes in each sheett

Hi Vusal,

I must be doing something wrong. I opened the workbook, pressed [alt-f11] in each sheet, pasted both codes in, and saved the file, but nothing happens.

Any thoughts?

I don't know which worksheet you pasted your code into; however, I have put it in the ThisWorkbook module and modified it slightly to ignore the "DATA" worksheet.

PHP Code: 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Data" Then Exit Sub
    On Error GoTo errorout   'Resume Next
    If Target = 1 Then
        With Target
            .AddComment
            .Comment.Text Text:=Application.UserName & ":" & Chr(10)
            .Comment.Visible = True
        End With
    End If
errorout:
    Err.Clear
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Data" Then Exit Sub
    
    Dim MyShp       As Comment, _
        CommentList As Comments
        
    Set CommentList = Sh.Comments
    For Each MyShp In CommentList
        MyShp.Visible = False
    Next MyShp

End Sub 


Originally Posted by vamedic11

Hi Ben, and thank you!

After working it, it looked GREAT...until I protected the sheets!!!

I suspect that adding comments, even to unlocked cells, is not possible in Excel 2007. Would I be correct?

Answered here:

http://www.excelforum.com/excel-work...worksheet.html