Comments based on criteria

desuth

New Member
Joined
Dec 5, 2017
Messages
21
Office Version
  1. 2016
I found this VBA on how to make it where comments are based on certain criteria, but i need it to do just a bit more and i am stumped. What i need it to do is to pick out a cell value for a range in column A and put the comment in the cell of the same row in column G. I need it to do this for 1000 rows.
Below is the code i have found that needs a modification.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim c As Comment

    On Error GoTo haveError

    'see if any changes are in the monitored range...
    Set rng = Application.Intersect(Target, Me.Range("C2"))

    If Not rng Is Nothing Then
    'Next line prevents code updates from re-triggering this...
    '  (Not really needed if you're only adding comments)
        Application.EnableEvents = False
        For Each cell In rng.Cells
               If cell.Value = "Croatia" Then
                   Set rng = ActiveSheet.Cells(4, 3)
                   If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
                   cell.Offset(2, 0).AddComment "Happy is as happy does"
                   'cell.Offset(0, 2).AddComment "fi"
                   'cell.Offset(0, 3).AddComment "fo"
               End If
              
               If cell.Value = "France" Then
                   Set rng = ActiveSheet.Cells(4, 3)
                   If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
                   cell.Offset(2, 0).AddComment "Maybe yes"
                   'cell.Offset(0, 2).AddComment "fi"
                   'cell.Offset(0, 3).AddComment "fo"
               End If
              
               If cell.Value = "Spain" Then
                   Set rng = ActiveSheet.Cells(4, 3)
                   If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
                   cell.Offset(2, 0).AddComment "Probably no"
                   'cell.Offset(0, 2).AddComment "fi"
                   'cell.Offset(0, 3).AddComment "fo"
               End If
              
                   For Each c In ActiveSheet.Comments
                     c.Visible = False
                   Next
              
            Next
       
        Application.EnableEvents = True
    End If
    Exit Sub

haveError:
    MsgBox Err.Description
    Application.EnableEvents = True

End Sub
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,129
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
 

desuth

New Member
Joined
Dec 5, 2017
Messages
21
Office Version
  1. 2016
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

i would but i'm on Gov computer and cant. Maybe this will help a little further understand.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim c As Comment

    On Error GoTo haveError

    'see if any changes are in the monitored range...
    Set rng = Application.Intersect(Target, Me.Range("C2"))

    If Not rng Is Nothing Then
    'Next line prevents code updates from re-triggering this...
    '  (Not really needed if you're only adding comments)
        Application.EnableEvents = False
        For Each cell In rng.Cells
               If cell.Value = "Dog" Then
                   Set rng = ActiveSheet.Cells(3, 1)
                   If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
                   cell.Offset(3, 4).AddComment "I like dogs"
                   'cell.Offset(0, 3).AddComment "fi"
                   'cell.Offset(0, 3).AddComment "fo"
               End If
               
               If cell.Value = "Cat" Then
                   Set rng = ActiveSheet.Cells(3, 1)
                   If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
                   cell.Offset(3, 4).AddComment "I dont like cats"
                   'cell.Offset(0, 3).AddComment "fi"
                   'cell.Offset(0, 3).AddComment "fo"
               End If
               
               If cell.Value = "Horse" Then
                   Set rng = ActiveSheet.Cells(3, 1)
                   If Not (rng.Comment Is Nothing) Then rng.Comment.Delete
                   cell.Offset(3, 4).AddComment "Horses are big"
                   'cell.Offset(0, 3).AddComment "fi"
                   'cell.Offset(0, 3).AddComment "fo"
               End If
               
                   For Each c In ActiveSheet.Comments
                     c.Visible = False
                   Next
               
            Next
        
        Application.EnableEvents = True
    End If
    Exit Sub

haveError:
    MsgBox Err.Description
    Application.EnableEvents = True

End Sub
 

Attachments

  • Comments.png
    Comments.png
    177.8 KB · Views: 4

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,129
In this line of code:
VBA Code:
Set rng = Application.Intersect(Target, Me.Range("C2"))
you set the value of 'rng'

In this line of code:
VBA Code:
Set rng = ActiveSheet.Cells(3, 1)
you set the value of 'rng' to something else. This would replace the previous setting.

Rather than trying to analyze your code, I think it might be easier to follow if you could explain in words, step by step, what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data that you posted. Your macro is a Worksheet_Change event which means that it will be triggered by a change in a cell. Which cells are you changing to trigger the macro?
 

desuth

New Member
Joined
Dec 5, 2017
Messages
21
Office Version
  1. 2016

ADVERTISEMENT

If in cell A3 says Dog, then i want Cell D3 to have a comment of "Dogs are good"
If in cell A4 says Cat, then i want Cell D4 to have a the comment of "Cats are bad"

i want this to repeat all the way down through the rows. The above VBA works for just one cell i need it to work for all of the rows.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,129
Are you entering the values in column A one cell at a time or are you copying/pasting more than one value at a time or a combination of both?
 

desuth

New Member
Joined
Dec 5, 2017
Messages
21
Office Version
  1. 2016

ADVERTISEMENT

Are you entering the values in column A one cell at a time or are you copying/pasting more than one value at a time or a combination of both?
most of the time i am entering the values one at a time from a drop down box. Sometimes i am copying a value from previous cells, and sometimes values in Column A doesnt have a value that needs a comment in Column D
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,129
sometimes values in Column A doesnt have a value that needs a comment in Column D
Are there specific values in column A that never need a comment or can those values vary? If there are specific values that never need a comment, then I would need to know what those values are. If it can vary, then there would have to be some criteria that decides which values need a comment and which values don't. Also, do you want the macro to be triggered automatically with a Worksheet_Change event or do you want a macro that you could run manually whenever you needed the comments?
 

desuth

New Member
Joined
Dec 5, 2017
Messages
21
Office Version
  1. 2016
Those values in Column A vary, there is no one value that doesn't need one. There only needs to be a comment for about 30 values in column A.
I have 30 different aircraft and when an aircraft is selected in column i want a criteria comment to pop up in column D.
The VBA code needs to automatically have the comment when the cells change. That would be the best so when someone enters the data in Column A they can see the criteria comment in column D.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,129
I can write a macro that will include a list of the aircraft that require a comment. I can use dummy names for now. However, you will have to substitute the dummy names in the macro with the actual names and keep those names in the macro up to date if there are any additions, deletions or changes in the future. Will that work for you?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,361
Members
414,233
Latest member
WolverineNurse

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
Top