Execl - Adding user comments based on cell contents

I have a spreadsheet with tabs for each month of a year and would like if a user enters "N" into a cell that Excel prompts the user to input a cell comment.  

Can someone help me with the VBA for this?

Thanks so much,

Mark
M ThomasAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
I corrected that line and modified the code a bit for when there's already a comment in the cell.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strComment As String

If UCase(Target) = "N" Then
    If Target.Comment Is Nothing Then
        strComment = InputBox("Please enter the text of your comment")
        Range(Target.Address).AddComment strComment
    Else
        If vbNo = MsgBox("A comment already exists in the cell. Do you want to replace it?", vbYesNo) Then
            Exit Sub
        Else
            strComment = InputBox("Please enter the text of your new comment")
            Range(Target.Address).Comment.Text Text:=strComment
        End If
    End If
End If
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strComment As String

If UCase(Target) = "N" Then
    strComment = InputBox("Please enter the text of your comment")
    If Target.Comment Is Nothing Then
        Range(Target.Address).Comment.Text Text:=strComment
    Else
        If vbNo = MsgBox("A comment already exists in the cell. Do you want to replace it?", vbYesNo) Then
            Exit Sub
        Else
            Range(Target.Address).Comment.Text Text:=strComment
        End If
    End If
End If
End Sub

Open in new window

0
 
M ThomasAuthor Commented:
Thanks Martin.

I had the VBA go to debug on the following line:

Range(Target.Address).Comment.Text Text:=strComment

Can you help?

Thanks so much,

Mark
0
 
M ThomasAuthor Commented:
Thanks so much Martin!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
1
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.

All Courses

From novice to tech pro — start learning today.