Hover over cell with dropdownlist

[Rudiji]

I searched but could not find a solution so here is my problem.

In sheet1 I have 2 columns with dropdownlist containing the names found in sheet2 column A.
I can choose 2 names each row to play against each other.
In sheet2 there are 2 other columns G and H which contain numbers eg. 35 and 16 which are connected to the name in the same row at column A.
What I want to do is when I pick a name in sheet1 and at any time hover over the name, it should do a HLOOKUP for that name in sheet2, find the numbers in that row and
open a small box with the message "OB: 35 3D: 16" as in the example above.
It should be hover over and not select the cell as there will always be one selected.

I hope this is clear as English is not my language.

Regards and thanks in advance,
Rudi

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook. To implement ..
1. Right click the Sheet1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rFound As Range
  
  If Not Intersect(Target, Columns("A:B")) Is Nothing And Target.CountLarge = 1 Then
    With Target
      .ClearComments
      If Len(.Value) > 0 Then
        Set rFound = Sheets("Sheet2").Columns("A").Find(What:=.Value, LookAt:=xlWhole)
        If Not rFound Is Nothing Then
          .AddComment
          .Comment.Text Text:="OB: " & rFound.Offset(, 6).Value & " 3D: " & rFound.Offset(, 7).Value
          .Comment.Shape.TextFrame.AutoSize = True
        End If
      End If
    End With
  End If
End Sub

 

[Rudiji]

Thank you so much Peter, this is exactly what I need.
Works perfect, you're the best.

Rudi

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[JoeH57]

I am looking for a similar solution, I tried adjusting the above code to fit my need but I was unsuccessful.

I have a list of values (cells N11:N##) that appear in a drop-down list on cell A#. I am trying to get the text associated with the drop-down list (cells O11:O##) to appear as a hover note.

What must I change to get the code to work for my situation? Thanks!

 

[Peter_SSs]

What must I change to get the code to work for my situation?

Welcome to the MrExcel board!

Try this code instead of the code provided earlier

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim DVType As Variant
  Dim rFound As Range
  
  If Not Intersect(Target, Columns("A")) Is Nothing And Target.CountLarge = 1 Then
    With Target
      On Error Resume Next
      DVType = .Validation.Type
      On Error GoTo 0
      If DVType = 3 Then
        .ClearComments
        If Len(.Value) > 0 Then
          Set rFound = Range("N11", Range("N" & Rows.Count).End(xlUp)).Find(What:=.Value, LookAt:=xlWhole)
          If Not rFound Is Nothing Then
            .AddComment
            .Comment.Text Text:=rFound.Offset(, 1).Value
            .Comment.Shape.TextFrame.AutoSize = True
          End If
        End If
      End If
    End With
  End If
End Sub

 

[JoeH57]

That worked perfectly. Is it possible in VB to make the hover note work when hovering over the each selection in the drop down list? The list I have is long, and the note is to help the user decide which value in the drop down applies. Instead of clicking each one to see the note, it would work better if they could see the note before selecting. Is this even a thing that can be done?