• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

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
0
M Thomas
Asked:
M Thomas
  • 3
  • 2
1 Solution
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
M ThomasAuthor Commented:
Thanks so much Martin!
0
 
Martin LissRetired ProgrammerCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now