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?

 

[Peter_SSs]

Is it possible in VB to make the hover note work when hovering over the each selection in the drop down list?

I don't believe that is possible. However, see if something like this might help.

In column P I have joined each item and its associates note/text.
The Data validation then uses column P. That way the user can see both when they are using the drop-down list.
When an item is chosen from the list the code splits the chosen value and puts the first part in the cell and the second part as the note.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim DVType As Variant, Bits As Variant
  
  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
          Bits = Split(.Value, "|")
          Application.EnableEvents = False
          .Value = Bits(0)
          .AddComment
          .Comment.Text Text:=Bits(1)
          .Comment.Shape.TextFrame.AutoSize = True
          Application.EnableEvents = True
        End If
      End If
    End With
  End If
End Sub

JoeH57.xlsm
	A	B												N	O	P
1
2
3
4
5
6
7
8
9
10
11														Item 1	Note 1	Item 1|Note 1
12														Item 2	Note 2	Item 2|Note 2
13														Item 3	Note 3	Item 3|Note 3
14														Item 4	Note 4	Item 4|Note 4
15														Item 5	Note 5	Item 5|Note 5
16														Item 6	Note 6	Item 6|Note 6
17
Sheet4

Cell Formulas
Range	Formula
P11:P16	P11	=N11&"|"&O11

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=$P$11:$P$16

 

[JoeH57]

I don't believe that is possible. However, see if something like this might help.

In column P I have joined each item and its associates note/text.
The Data validation then uses column P. That way the user can see both when they are using the drop-down list.
When an item is chosen from the list the code splits the chosen value and puts the first part in the cell and the second part as the note.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim DVType As Variant, Bits As Variant
 
  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
          Bits = Split(.Value, "|")
          Application.EnableEvents = False
          .Value = Bits(0)
          .AddComment
          .Comment.Text Text:=Bits(1)
          .Comment.Shape.TextFrame.AutoSize = True
          Application.EnableEvents = True
        End If
      End If
    End With
  End If
End Sub

JoeH57.xlsm
	A	B												N	O	P
1
2
3
4
5
6
7
8
9
10
11														Item 1	Note 1	Item 1|Note 1
12														Item 2	Note 2	Item 2|Note 2
13														Item 3	Note 3	Item 3|Note 3
14														Item 4	Note 4	Item 4|Note 4
15														Item 5	Note 5	Item 5|Note 5
16														Item 6	Note 6	Item 6|Note 6
17
Sheet4

Cell Formulas
Range	Formula
P11:P16	P11	=N11&"|"&O11

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=$P$11:$P$16

View attachment 122563 View attachment 122564

Thanks, I appreciate the quick response. I will try and incorporate something like that. My notes are several paragraphs long, so a single cell doesn't quite work but I can play with it and see if there is a way to make it simple. Thanks again, you are a great help!

 

[Peter_SSs]

Cheers. Hope you can make something useful out of it.