Check out this cool code I had help with. Works great if you have a list of names and addresses used for Data Validation and you need to have additional customer info. Take a look
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next ' Ignore VBA Error If Trying To Delete
' A Comment That Does't Exist
If Target = Worksheets("Sheet2"
.Range("ValdCell" Then ' If Cell With Validation Changes
With Target
.Comment.Delete ' Delete any Existing Attached Comment
' Find Comment On List And Add It To Cell
.AddComment.Shape.TextFrame.Characters.Text = _
Worksheets("Vlookup".Range("List".Find(Target).Comment.Shape.TextFrame.Characters.Text
End With
End If
End Sub
For newbies (like me) the two named ranges are "List" which is the worksheet that contains the data validation names and address list, and "Valdcell" which is where you want to place the data validation on any other worksheet.
Please test the code, see what you can add to it. I will be working on Hiding the comments box until need, an easy way to edit the comments box (for the end user) and a way to add the comments box on a USERFORM for when you make additions to the list thru a USERFORM.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next ' Ignore VBA Error If Trying To Delete
' A Comment That Does't Exist
If Target = Worksheets("Sheet2"
.Range("ValdCell" Then ' If Cell With Validation ChangesWith Target
.Comment.Delete ' Delete any Existing Attached Comment
' Find Comment On List And Add It To Cell
.AddComment.Shape.TextFrame.Characters.Text = _
Worksheets("Vlookup"
.Range("List".Find(Target).Comment.Shape.TextFrame.Characters.TextEnd With
End If
End Sub
For newbies (like me) the two named ranges are "List" which is the worksheet that contains the data validation names and address list, and "Valdcell" which is where you want to place the data validation on any other worksheet.
Please test the code, see what you can add to it. I will be working on Hiding the comments box until need, an easy way to edit the comments box (for the end user) and a way to add the comments box on a USERFORM for when you make additions to the list thru a USERFORM.
