How to auto populate comment box when cell value changes
Category: [Excel] Demo Available
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.
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to auto generate invoice number |
| How to auto-generate unique ID in Excel VBA UserForm |
| How to create automatic removal of empty rows |
| How to use a macro to auto delete file when passed 15 days |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.