comments for drop down list items?

I have a drop down list of people's initials for work orders. Basically the user selects the initials of the person who issued the work order. I'm using a validated list and have the list items stored on a separate sheet. Is there a way to add comments to the initials so if you mouse over it you can see who the person's name is. I.e. You select the initials JS and when you mouse over it it says "John Smith". However, this is a long column of listings, so I'd like it to do it for each set of initials selected.

I don't believe this can be done.

zoneblitz61,

I don't know if this is what you're looking for, but it may give you some ideas. It was posted sometime ago and I used it for a call index on the office computer. Take a look. Hope it helps.

Dean

Hello zoneblitz61,

This actually involves quite a few techniques to make it work. Fortunately, there are no hooks or API calls needed to make it work. You should refer to Sheet1 of the attached workbook to follow what I am saying.

The worksheet contains 2 named ranges: Initials and Names, one helper cell in E2 and a validation drop down list in cell G2 that displays Initials.

The helper cell has the following formula:

   =INDEX(Names,MATCH(G2,Initials, 0))

This formula returns the name for the selected initials.

The next piece of the puzzle is the Worksheet_Change event macro. This will place the contents of the helper cell (name) into the comment text. If the validation doesn't contain a comment, the macro will create one.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$G$2" Then
    Set Cmnt = Target.Comment
    If Cmnt Is Nothing Then
      Target.AddComment Text:=Cells(2, "E").Text
    Else
      With Cmnt
        .Text Text:=ActiveSheet.Cells(2, "E").Text
      End With
    End If
  End If
  
End Sub

Sincerely,
Leith Ross

Thanks so much , this is all what i need as well.

Hi Leith Ross,

This is very useful, thank you very much. However from the below code it is only for cell G2 (where the drop down is), what if need to drag the drop down comments with validation to the whole column i.e. $G$:$G$ -- as it tried it this way and the range does not work.

Thanks

Hey Leith,

Thanks for this method! Would it be possible to use this for pictures vs text in the comment box? I have a parts spreadsheet that uses dropdowns and I'd like to have the picture show up when the mouse hovers over the drop down selection. Same style as you worked it out but using pictures vs text.

Thanks,