Create a dynamic Excel tooltip based on drop down and cell options.

[ExcellyMan]

Hi there,

First time poster here. I've been asked to design a spreadsheet for work that tracks delays on certain trucks for the days of the week. Under each day, the cell has a drop down option denoting either "On Time", "Delay", or "Cancelled". Here's an example:

Truck	Monday	Tuesday	Wednesday	Thursday
A	On Time	Cancelled	On Time	On Time
B	On Time	On Time	Delay	Delay
C	On Time	Delay	On Time	On Time

What I'd like is to have a tooltip value show up when I hover over a cell ONLY when "Delay" has been picked from the drop down. Additionally, I want that tooltip value to reference a particular cell elsewhere on the worksheet containing a specific delay reason since not all the cells reading "Delay" will have the same delay codes. So for example, a tooltip value when picking "Delay" for Truck C on Tuesday would show a value of "Traffic" while a tooltip value when picking "Delay" for Truck B on Wednesday may show "Driver got lost". The delay values would be populated elsewhere on the sheet but I would simply be referencing them. I've been scouring the web, trying to look up ways to do this but haven't really found what I'm looking for. If anyone is able to help me in any way I'd greatly appreciate it. Also, if further clarification is needed please let me know and I'd be happy to elaborate further.

Thanks.

 

[sijpie]

Open the module for the sheet that contains your table (right click on the sheet name tab, then select 'view code')
Paste the following code into the VBA window that opens. Then read through the comments, in particular about the table name or address. Modify to suit then test the code as is.

After you see that it works you need to add some code to get the correct text to add into the comment

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count > 1 Then Exit Sub  'ignore if user modifies more then one cell (copy paste of range for instance)
    ' See if the modified cell falls within the table with truck movements.
    ' The table should be given a name , or you can replace 'MyTable'(below) with table address
    If Not Intersect(Target, Range("MyTable")) Is Nothing Then
        With Target
            If .Value Like "Delay" Then 'the modified cell (target) now contains 'Delay'
                .AddComment
                .Comment.Visible = False
                'The text that you want to add to the note, needs to be compiled by you in some additional code
                .Comment.Text Text:="This is note with text"
            Else                        'the modified cell (target) contains other value than 'Delay'
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
            End If
        End With
    End If
End Sub