Custom Toooltips in Dashboards!

Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)

Question from P3 Adaptive School!

Got a great question the other day from Oscar, a student in P3 Adaptive School…

[OSCAR]:  “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”

My first thought was “no, not possible.”  Then ten seconds later, a guerilla-style hack came to mind.  And then, my reply:

[ROB]:  “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”

The Trick:  Hyperlinks to Nowhere!

Select a cell and choose Insert Hyperlink…

Your Hyperlink in Cell D5 Goes to Cell D5, Making the Hyperlink a “Do Nothing” Link

Now click on ScreenTip…

ScreenTip Button Lets You Enter a Tooltip

And that’s it!

You now have an “on hover” tooltip for cell D5!

End of blog post, right?

“Rob you SUCK!  That’s incredibly tedious!”

Yeah, who wants to go through that process for every cell.  Blech.

So, we create a “mirror” sheet, that has the same shape as our dashboard:

“Mirror” Sheet Has Same Shape and Location as Our Dashboard.
You Enter Desired Tooltips Here.

Then you MACRO That Sucker!

Go back to your dashboard sheet, make sure your mirror sheet is named “ToolTIps,” then run this macro called RunTheToolTipHack.  It calls two other macros, also included below.

This macro “grabs” the tooltips from your mirror sheet and assigns them all to the cells on your main sheet.  It’s magic.

(Anyone who makes fun of my macro code will be beaten with a nerf bat.  Or worse, asked to improve it.)

Sub RunTheToolTipHack()
    CubeFormulasToolTips ActiveSheet.Name, “ToolTips”
End Sub

Sub CubeFormulasToolTips(sSheet As String, sToolTipsSheet As String)
    Dim oMainSheet As Worksheet
    Dim oTipsSheet As Worksheet
    Dim oRange As Range
    Dim c As Range
    Dim sFormula As String
    Dim sAddress As String
    Dim sToolTip As String
   
    Set oMainSheet = Sheets(sSheet)
    Set oTipsSheet = Sheets(sToolTipsSheet)
   
    Set oRange = oMainSheet.UsedRange
   
    For Each c In oRange.Cells
        sFormula = c.FormulaR1C1
        sAddress = c.Address
        sToolTip = oTipsSheet.Range(sAddress).Value
        If Left(sFormula, 5) = “=CUBE” Then
            SetHyperlink sSheet, sAddress, “‘” & sSheet & “‘!” & sAddress, sToolTip, True, False
        End If
    Next
End Sub

Sub SetHyperlink(sSheet As String, sCell As String, sDestAddress As String, sToolTip As String, bFormula As Boolean, bLookLikeLink As Boolean)
    Dim oSheet As Worksheet
    Dim sFormat As String
    Dim c As Range
    Dim iColor As Integer
   
    Set oSheet = ActiveWorkbook.Sheets(sSheet)
    Set c = oSheet.Range(sCell)
    sFormat = c.NumberFormat
    iColor = c.Font.ColorIndex
   
    If bFormula = True Then
        oSheet.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:=sDestAddress, ScreenTip:=sToolTip
    Else
        oSheet.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:=sDestAddress, TextToDisplay:=c.Value, ScreenTip:=sToolTip
    End If
   
    c.NumberFormat = sFormat
   
    If bLookLikeLink = False Then
        c.Font.Underline = xlUnderlineStyleNone
        c.Font.Color = iColor
    End If
   
End Sub

Questions

1. Does this work with pivots?  No, sadly, pivots don’t let you set hyperlinks on the cells.  In Power Pivot land, this only works with cube formula reports.
2. Can the mirror sheet be constructed using formulas?  Yes, absolutely.  No need to manually enter tooltips.  In fact you could use cube formulas and text measures to FETCH COMMENTS FROM THE POWER PIVOT MODEL ITSELF!  Cue maniacal laughter!!!!
3. Will these tooltips work in the web version of Excel?  Yes, they will.  I have tested it.
4. Wait, I didn’t think macros ran on the web version!  That’s right, they don’t.  But your macros don’t need to RUN on the server.  You are merely using the macros to SET the tooltips.  The macros don’t even need to live in your report workbook, they can live in your Personal Macro workbook.
5. What happens when I want to change the comments?  Well, you need to run the macros again.
6. Wait, even if I am using formulas to fetch the comments???  Yeah.  You refresh your Power Pivot model, refresh the dashboards, and even refresh the mirror sheet.  Everything is updated.  But your tooltips will still be the “stale” tips until you run the macro again.
7. So this won’t work with Power Pivot autorefresh?  Bingo.  It won’t.  If you have a Power Pivot server and have scheduled the reports to autorefresh, the tooltips will still be stale.  You will need to download the file and re-run the macros.
8. That’s a tragedy.  Can you fix that with the HYPERLINK function?  These are good questions, it’s like you were watching me work on this over my shoulder or something.  No, the HYPERLINK function lacks an input for tooltip, otherwise we COULD do this all automatically.
9. Shouldn’t we point this out to Microsoft and ask them to address this tragic shortfall?  Yup, damn straight.  On it.
10. Why not just have a TOOLTIP() function and dispense with all of this fake hyperlink crap?  Doubly damn straight, and doubly on it.

You Have to be a Real Nerd to Be Disappointed About This.  Which, I am.

Am I Missing Something?

I have this spider sense feeling that a longtime Excel pro is going to now say “but Rob why didn’t you do X instead?”  I am actually hoping that there IS a better approach here, and this post will bring that to the surface.  So bring it on.