Hover over cell with dropdownlist

Rudiji

New Member
Joined
Sep 5, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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
 
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).

VBA Code:
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
 
Upvote 0
Solution
Thank you so much Peter, this is exactly what I need.
Works perfect, you're the best.

Rudi
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
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!
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
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.

VBA Code:
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
ABNOP
1
2
3
4
5
6
7
8
9
10
11Item 1Note 1Item 1|Note 1
12Item 2Note 2Item 2|Note 2
13Item 3Note 3Item 3|Note 3
14Item 4Note 4Item 4|Note 4
15Item 5Note 5Item 5|Note 5
16Item 6Note 6Item 6|Note 6
17
Sheet4
Cell Formulas
RangeFormula
P11:P16P11=N11&"|"&O11
Cells with Data Validation
CellAllowCriteria
A2:A10List=$P$11:$P$16


1740031660058.png
1740031704583.png
 
Upvote 0
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.

VBA Code:
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
ABNOP
1
2
3
4
5
6
7
8
9
10
11Item 1Note 1Item 1|Note 1
12Item 2Note 2Item 2|Note 2
13Item 3Note 3Item 3|Note 3
14Item 4Note 4Item 4|Note 4
15Item 5Note 5Item 5|Note 5
16Item 6Note 6Item 6|Note 6
17
Sheet4
Cell Formulas
RangeFormula
P11:P16P11=N11&"|"&O11
Cells with Data Validation
CellAllowCriteria
A2:A10List=$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!
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top