Solved

Execl - Adding user comments based on cell contents

Posted on 2016-11-15
5
32 Views
Last Modified: 2016-11-16
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
Comment
Question by:M Thomas
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:M Thomas
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:M Thomas
Comment Utility
Thanks so much Martin!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now