Auto-Add New Comment to Cell With Content

default_name

Board Regular
Joined
May 16, 2018
Messages
180
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hello!

Is there a way to automatically add a comment to a cell within a certain range if the cell has content?
Here is an example of what I mean...(I cant share the actual content, so hopefully this example explains my idea)

I set up a calendar to keep track of food that is eaten.
If a person eats breakfast, then the Breakfast box for that day is checked and a comment is added to that cell so that the user may input what was eaten for breakfast.
In this table, for example, the person ate Breakfast on Monday (as shown by the non-blank cell). In the Monday Breakfast cell I would want a New Comment to be added so that the user may input the foods that were eaten. I do not want comments to be added to the cell if they are blank (Monday Lunch for example). I would only want these behaviors to take place in cells B2:H4 (in the example shown).
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sun
[/TD]
[TD="align: center"]Mon
[/TD]
[TD="align: center"]Tue
[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu
[/TD]
[TD="align: center"]Fri
[/TD]
[TD="align: center"]Sat
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]Breakfast
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]Lunch
[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]Dinner
[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I hope that makes sense.
Thanks in advance for your help and let me know if I need to clarify anything.
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an "x" in any cell in B2:H4. and exit the cell.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:H4")) Is Nothing Then Exit Sub
    Dim response As String
    response = InputBox("Please enter what you had for " & Cells(Target.Row, 1) & " separated by commas.")
    Target.AddComment response
End Sub
 
Upvote 0
This version offers a bit more flexibility to the user than the code Mumps posted. The user can delete an "x" and the cell comment is removed. The user can copy/paste multiple x's and each cell receiving a paste will prompt the user to enter a comment.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range, Cmnt As String
Set R = Range("B2:H4")
If Not Intersect(Target, R) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, R)
        If c.Value = "" Then c.ClearComments
Again:      If LCase(c.Value) = "x" Then
            If c.Comment Is Nothing Then
                Cmnt = InputBox("Enter a comment for cell " & c.Address(0, 0), "INPUT REQUIRED")
                If Cmnt = "" Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                c.Addcomment
                c.Comment.Text Text:=Cmnt
            Else
                c.ClearComments
                GoTo Again
            End If
        End If
    Next c
End If
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,881
Messages
6,175,161
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top