I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option Explicit vba blog blogged about this more than a year ago. He made an amazing Periodic Table of Elements.

I used his technique in this basic chart.

My values are in a table in a different sheet, the user defined function filters column 1. I found out that you can´t clear a table filter from the user defined function:

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1 won´t work!

I had to use "*" to show all values in table column 1.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
Field:=1, Criteria1:="*", Operator:=xlFilterValues

VBA code

Function MouseHover(str As String, bln As Boolean)
If bln = False Then
    If str = "Deselect all" And Sheet2.Range("I11") <> str Then
        Sheet2.Range("I11") = "Deselect all"
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:="", Operator:=xlFilterValues
        Exit Function
    End If
    If Sheet2.Range("I11") <> str Then
        Sheet2.Range("I11") = str
        ActiveSheet.ChartObjects("Chart 4").Chart.SetSourceData Source:=Sheets("Sheet1").Range("Table1[#All]")
        ActiveSheet.ChartObjects("Chart 4").Chart.PlotBy = xlRows
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:=str, Operator:=xlFilterValues
    End If
Else
    If Sheet2.Range("I11") <> str Then
        Sheet2.Range("I11") = CStr(str)
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:="*", Operator:=xlFilterValues
        ActiveSheet.ChartObjects("Chart 4").Chart.SetSourceData Source:=Sheets("Sheet1").Range("Table1[[#All],[Column1]], Table1[[#All],[" & str & "]]")
        ActiveSheet.ChartObjects("Chart 4").Chart.PlotBy = xlColumns
    End If
End If
End Function

The user defined function saves a value in cell I11. I made it invisible by applying a custom formatting to it: ;;;

Formula in cell I8:

I wanted to remove the hyperlink effect so I added "Apple"& to the formula.

Download excel *.xlsm file

Interactive chart - mouse hover.xlsm

Related posts:

Interactive chart in excel (vba)

Quickly filter a column in an excel table

Copy excel table filter criteria (vba)

Search two related tables simultaneously (vba)

Dynamic chart – Display values from a table row or column