How to Auto populate comment box when cell value changes

How to auto populate comment box when cell value changes

Requirement:

The user has a workbook with 2 sheets.

Sheet 2 has four columns named as follows: ( A) Customer Name, (B) Address, (C)Telephone Number and (D) Mobile Number.

The user would like to type a customer name in sheet 1, column A listed in Sheet 2 under column (A) and then have the cell in sheet one auto populate a comment box with the relevant address, telephone number and mobile number.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1204972-auto-populate-comment-box-when-cell-value-changes

Solution:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh2 As Worksheet, a As Long, lr2 As Long
Set sh2 = Sheets("Sheet2")
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(sh2.Range("A1:A" & lr2), Cells(Target.Row, Target.Column).Value) <> 0 Then
Select Case Target.Column
Case 2, 3, 4, 5, 6
    a = sh2.Columns(1).Find(Target.Value, , , 1).Row
        With Cells(Target.Row, Target.Column)
            If Not .Comment Is Nothing Then .Comment.Delete
            .AddComment
            .Comment.Text Text:=sh2.Cells(a, 2).Value & vbLf & _
            sh2.Cells(a, 3).Value & vbLf & _
            sh2.Cells(a, 4).Value
        End With
Case Else
End Select
End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by jolivanes.