Comments based on criteria

[desuth]

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.

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

 

[mumps]

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]

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.

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

 

[mumps]

In this line of code:

Set rng = Application.Intersect(Target, Me.Range("C2"))

you set the value of 'rng'

In this line of 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]

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]

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]

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]

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]

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]

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?