Can I display comments in a validation list

S

Stoke

I am new to using these Validation lists - I have comments associated with
the validation list, and I am wondering if there is a way to bring the
comment into the target cell with the contents of the Validation list cell
when it is selected from the drop-down box. Thanks
 
D

Dave Peterson

Maybe...

You might be able to use a macro...

I created a list on Sheet1. I named it myList (to be used with
data|validation).

Then on sheet2, I used data|validation in A1 and pointed to that list on sheet1.

Place the code in the sheet2 worksheet module.

rightclick on the worksheet tab with the data validation. Select view code.
Paste this in the new code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
Dim myList As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

'clear previous comment
If Target.Comment Is Nothing Then
'do nothing
Else
Target.Comment.Delete
End If

Set myList = Worksheets("sheet1").Range("mylist")

res = Application.Match(Target.Value, myList, 0)

If IsError(res) Then
'something very bad happened
Else
If myList(res).Comment Is Nothing Then
'do nothing
Else
Target.AddComment Text:=myList(res).Comment.Text
End If
End If

End Sub

Then back to excel and test it out.

(This won't work with xl97.)
 
Top