VBA Code to Add a Comment to a Cell based on that Cells Content

[albert_de]

Hi

I do not know VBA and I am hoping that someone may be able to guide me with some coding. I have been searching a solution for a week or so, and they are out there. I just don't know how to apply that coding to my project.

The setup:

1. In Column AN20:AN26, I have a list of abbreviations (e.g. DD, DC, T, etc.)
2. In Column AO20:AO26, I have the definitions for those abbreviations (e.g. Direct Debit, Direct Credit, Transfer,etc.). These are exactly adjacent to the list of abbreviations in Column AN20:AN26
3. In Column B9:B12 and B19:B200, I have a drop down list that contains the abbreviations (e.g. DD, DC, T,etc.) from the list in Column AN20:AN26 (I don't think it matters to this, but these abbreviations have Text and Fill Conditional Format Rules attached to them in this column).

I would like to be able to do the following:

When selecting an abbreviation from the Drop Down List in Column B9:B12 and B19:B200, I would like to automatically assign the matching definition (from Column AO20:AO26) as a Comment in that cell.
e.g.
1. In Cell B9 - DD is selected from the Drop Down List
2. Then, Direct Debit is the automatically added as a comment to Cell B9.

Rules:
1. If a Cell in Column B9:B12 and B19:B200 = empty or is changed to be made empty, then there should be no comment or the existing comment is removed.
2. If a Cell in Column B9:B12 and B19:B200 = contains a selected abbreviation, then the matching definition should be added as a comment in that cell
3. If a Cell in Column B9:B12 and B19:B200, already contains an abbreviation but that abbreviation is changed, then the matching definition of the changed cell should be refreshed to match the changed cell.

Thanks to Member "Akuini", this workbook already has some VBA code in it (Private Sub Worksheet_Change(ByVal Target As Range)). I am hoping to learn how to add your solution to my query to "Akuni's" code.

Thank you for your help.

 

[albert_de]

Sorry, I forgot to include a formatting Rule (see "4". below:

Rules:
1. If a Cell in Column B9:B12 and B19:B200 = empty or is changed to be made empty, then there should be no comment or the existing comment is removed.
2. If a Cell in Column B9:B12 and B19:B200 = contains a selected abbreviation, then the matching definition should be added as a comment in that cell
3. If a Cell in Column B9:B12 and B19:B200, already contains an abbreviation but that abbreviation is changed, then the matching definition of the changed cell should be refreshed to match the changed cell.
4. Is it possible to format the Comment text as a part of the VBA? I would like to use: Font - Calibri Light, Font Size - 11, Italic

 

[kevin9999]

Please try the following on a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B9:B12,B19:B200"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Target = "" Then
            With Target
                If Not .Comment Is Nothing Then .Comment.Delete
            End With
        Else
            With Target
                Dim s As String
                s = WorksheetFunction.VLookup(Target, Range("AN20:AO26"), 2, False)
                With .AddComment
                    .Text s
                    With .Shape
                        With .TextFrame.Characters.Font
                            .Name = "Calibri Light"
                            .Italic = True
                            .Size = 11
                        End With
                    End With
                End With
            End With
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[albert_de]

Hi Kevin,

Thank you for your time.

This code works once .
It seems to fail when changing an Abbreviation in the Column B Drop Down List / Cell?
Closing the Spreadsheet without saving and reopening resets the script but it only works if you do not change any the abbreviations that have previously been assigned to the Column B Drop Down List's Cell

When it is working I noticed that:
1. the Comment was assigned correctly, but if the Abbreviation was removed or changed that the Comment did not update to match the change. Or,
2. when attempting to change an Abbreviation from the Drop Down List a VBA Runtime Error occurs- Runtime Error "1004": Application-defined or object-defined error. Debug shows the highlighted line of code: "With .AddComment "



I also need to learn how to run multiple VBA codes on the same Worksheet, as this is the second piece of VBA. The first is:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("F17:F200")) Is Nothing Then

Application.EnableEvents = False
If Target = "" Then
Target.Offset(0, 1) = ""
Else
Target.Offset(0, 1) = "Select Name>>"
End If
Application.EnableEvents = True

End If

End Sub

 

[albert_de]

Hi Kevin,

Also, when the code is working, Rule 1 doesn't seem to work.
That is, when clearing the Abbreviation the Comment is not removed.

 

[kevin9999]

Please try the mended code (additional line added to remove existing comment)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B9:B12,B19:B200"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Target = "" Then
            With Target
                If Not .Comment Is Nothing Then .Comment.Delete
            End With
        Else
            With Target
                If Not .Comment Is Nothing Then .Comment.Delete
                Dim s As String
                s = WorksheetFunction.VLookup(Target, Range("AN20:AO26"), 2, False)
                With .AddComment
                    .Text s
                    With .Shape
                        With .TextFrame.Characters.Font
                            .Name = "Calibri Light"
                            .Italic = True
                            .Size = 11
                        End With
                    End With
                End With
            End With
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[albert_de]

Hi Kevin

That's it
It is working exactly as requested!
Thank you very much

 

[kevin9999]

Happy to help, and thanks for the feedback

 

[albert_de]

Hi Kevin,

Something has changed with the way the Drop Down List is displayed since reopening the spreadsheet today.

I have a Dual Monitor setup. When using Excel on the main monitor (right hand side) for some reason now the Drop Down List in Column B is displaying very wide on Monitor 2 (left hand monitor).

I'd prefer the Drop Down List to be displayed on the sheet and not on another monitor.

New behaviour:

Original behaviour:

Could the new code have changed this behaviour? Or is this something else?

Is there a way to display the Drop Down List on the sheet again and to also confine the Drop Down Width to 4 or to match the Width of Column AN?

Thank you for any help with this.

 

[kevin9999]

Could the new code have changed this behaviour? Or is this something else?

There's nothing in the code that would have caused this. The width of the data validation dropdown is generally tied to the column with that the data validation cell sits in (not the column with the data validation 'list'). I don't use dual monitors so I can't be of much help there. Perhaps if you start a new thread with this specific issue it may draw more informed attention.