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.

How to use the user defined function

I used his technique in this basic chart, see above animated image. I simply move the mouse pointer over a cell and the chart changes instantly, there is really nothing more to it. No need to press with left mouse button on cell to select it or anything like that.

Excel defined Table

My data is converted to an Excel defined Table located on a different sheet, shown in the picture below.

The great thing about Excel defined Tables is that they grow automatically if more data is added, you don't have to change cell references in formulas.

Select any cell in the data set.
Go to tab "Insert".
Press with mouse on "Table" button.
Press with left mouse button on OK button.

Hide value in cell I11

The user defined function saves a value in cell I11. I made it invisible by applying custom formatting to it: ;;; The value is still there, however, you can't see it on the worksheet unless you select the cell and check out the formula bar.

Select cell I11.
Press CTRL + 1 to open the Format Cell dialog box.
Make sure you are on tab "Number".
Press with left mouse button on Category: Custom to select it.
Type ;;; (see image above).
Press with left mouse button on OK button.

Formulas

Formula in cell I8:

="Apple"&IFERROR(HYPERLINK(MouseHover("Apple", FALSE),""),"")

Explaining formula in cell I8

Step 1 - User defined function

The user defined function MouseHover is triggered when the mouse pointer hovers over the cell. The first argument tells the UDF which value to use in order to sort the Excel defined Table. The second argument tells the UDF to either sort or change chart data source.

MouseHover("Apple", FALSE)

Step 2 - Create Hyperlink

The HYPERLINK function triggers the UDF when the mouse pointer is over a cell.

HYPERLINK(link_location, [friendly_name])

HYPERLINK(MouseHover("Apple", FALSE),"")

Step 3 - Remove error

The IFERROR function allows you to hide errors.

IFERROR(HYPERLINK(MouseHover("Apple", FALSE),""),"")

Step 4 - Remove hyperlink underline

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

"Apple"&IFERROR(HYPERLINK(MouseHover("Apple", FALSE),""),"")

Formula in cell I3:

="2008"&IFERROR(HYPERLINK(MouseHover("2008", TRUE),""),"")

The user defined function MouseHover filters column 1 if mouse pointer is over any cell in cell range I8:I10. The chart data range is changed if mouse pointer hovers over any cell in cell range I3:I7 based on the value in the given cell.

The bln argument determines what to do, TRUE changes the data source range based on variable str and FALSE filters the Excel defined Table based on variable str.

Conditional Formatting

A conditional formatting formula highlights the value currently being hovered over, it simply compares the "invisble" value in cell I11 with the value in eac cell in cell range I8:I10.

=$I$11=I8

VBA code

The boolean value bln determines if

'Name user defined function and dimension arguments and declare data types
Function MouseHover(str As String, bln As Boolean)

'Check if boolean variable bln is equal to False
If bln = False Then

    'Check if variable str is equal to "Deselect all" and cell value in cell I11, sheet2 is not equal to variable str
    If str = "Deselect all" And Sheet2.Range("I11") <> str Then

        'Save value "Deselect all" to cell I11 on sheet2
        Sheet2.Range("I11") = "Deselect all"

        'Apply Autofilter to Excel defined table named Table 1 
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:="", Operator:=xlFilterValues

        'Stop user defined function
        Exit Function
    End If

    'Check if value in cell I11 (sheet2) is not equal to variable str
    If Sheet2.Range("I11") <> str Then

        'Save value in variable str to cell I11 (sheet2)
        Sheet2.Range("I11") = str

        'Change data source to Excel defined table named Table1
        ActiveSheet.ChartObjects("Chart 4").Chart.SetSourceData Source:=Sheets("Sheet1").Range("Table1[#All]")

        'Plot data by rows on chart 4
        ActiveSheet.ChartObjects("Chart 4").Chart.PlotBy = xlRows

        'Use value in variable str as a condition in Excel defined Table Table1 and applied to first column
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:=str, Operator:=xlFilterValues
    End If

'Proceed here if boolean variable bln is not equal to False
Else

    'Check if value in cell I11 (sheet2) is not equal to variable str
    If Sheet2.Range("I11") <> str Then

        'Convert value in variable str to string and save it to cell I11 (sheet2)
        Sheet2.Range("I11") = CStr(str)

        'The asterisk character makes the Excel defined Table show all values
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:="*", Operator:=xlFilterValues

        'Change data source based on first argument
        ActiveSheet.ChartObjects("Chart 4").Chart.SetSourceData Source:=Sheets("Sheet1").Range("Table1[[#All],[Column1]], Table1[[#All],[" & str & "]]")

        'Change chart settings so it sets the way columns or rows are used as data series on the chart.
        ActiveSheet.ChartObjects("Chart 4").Chart.PlotBy = xlColumns
    End If
End If
End Function

Where to put the code?

Create a backup of your workbook.
Copy above VBA code.
Press Alt + F11 to open the Visual Basic Editor.

Final notes

I found out that you can't clear a table filter from a user defined function:

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

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

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

26 Responses to “How to use mouse hover on a worksheet [VBA]”

Jordan says:

August 7, 2012 at 6:03 pm

Hey - thanks for the mention!

This site is a great resource, too.

Reply
Gerald says:

August 8, 2012 at 12:23 pm

This is really neat!

I have so many ideas but have so much to learn.

This is a great site!

Reply
Oscar says:

August 8, 2012 at 1:23 pm

Jordan and Gerald,

Thanks!

Reply
Jordan says:

August 8, 2012 at 4:31 pm

By the way, if you want to add labels to your hyperlinks that won't redirect the user when pressed with left mouse button , you can write it as:

IFERROR(HYPERLINK(MouseHover("Apple", FALSE),"Apple"),"Apple")

In lieu of concatenating above

J

Reply
chrisham says:

August 15, 2012 at 1:21 pm

Oscar, how do you get the mouse over event to kick in?

Reply
chrisham says:

August 15, 2012 at 1:39 pm

duh..........sorry I figured that out, but thanks for this cool tip!

Reply
Oscar says:

August 15, 2012 at 2:20 pm

Jordan,

Thanks!

chrisham,
I forgot to mention that! Conditional formatting compares the value in cell I11.

Reply
Shiven says:

September 6, 2012 at 6:29 am

Hi, its really gr8 resource for learner and developer. I am facing one problem whenever i m trying to open the .xlsm file getting .zip file and finally after extracting getting .xlm files from which not getting the impact. which is talked about.

Please help, n thanx.

Shiven

Reply
Rudy says:

June 7, 2013 at 2:04 pm

Tutorial Please

Reply
Rudy says:

June 9, 2013 at 5:48 am

Is it posible to create this interactive chart into interactive candlestick chart to compare two or more chart ?

Reply
- Oscar says:
  
  June 10, 2013 at 8:50 am
  
  Rudy,
  
  Yes, I believe so. Hopefully I´ll have a tutorial ready this week.
Interactive candlestick chart | Get Digital Help - Microsoft Excel resource says:

June 14, 2013 at 1:58 pm

[...] Rudy asks in this post Use a mouse hovering technique to create an interactive chart: [...]

Reply
The Excel Rollover Mini FAQ | Option Explicit VBA says:

September 13, 2013 at 2:05 am

[…] Tutorial on Interactive Dashboard using Hyperlinks by Chandoo Use a mouse hovering technique to create an interactive chart by Oscar of […]

Reply
Caio says:

June 24, 2014 at 2:29 pm

I am a noob on VBA, so I have limited skills on this. I was able to apply the VBA perfectly in my Worksheet, however, I need to use the hover effect in two ranges that are in different sheets in the same workbook.
What should I do to make the code different and make it works? Appreciate any help

Reply
Jordan Goldmeier says:

June 24, 2014 at 10:37 pm

Caio,

If you need to create another hover range on another worksheet, you can simply make a second UDF rollover function. Then, in that new range, make sure you point to that new UDF function.

Reply
Bob says:

August 15, 2014 at 5:01 pm

Please could you help me understand how to get the hover area to be the entire cell rather than the just the text? I have added some extra cells for the reporting that I need but the hover area is only for the text and everything that I have tried has not worked.

Thanks

Reply
CasesST says:

September 10, 2014 at 9:24 am

Please Help!
I'm very extremely basic in my knowledge of VBA, where is it that the macro is being assigned exactly? I've opened the file but code is not appearing to find this?
thank you

Reply
- Oscar says:
  
  September 10, 2014 at 2:19 pm
  
  CasesST,
  
  I've opened the file but code is not appearing to find this?
  1. Open the VB Editor (Press Alt+F11).
  2. Double press with left mouse button on Modules in the Project Explorer window.
  3. Double press with left mouse button on Module1.
- CasesST says:
  
  September 11, 2014 at 8:28 am
  
  Thanks for your help.
  This part seems to be fine... though when I'm hovering over the hyperlinked cells nothing is happening, I'm using cell Q13 as oppposed to I11 which I have amened the vba to accomodate this but nothing appears in this cell.
  Any ideas?
  
  Thank you
Best of Excel on the Web – Hidden Treasures (2016) says:

January 5, 2016 at 1:50 pm

[…] Use a mouse hovering technique to create an interactive chart […]

Reply
Best of Excel on the Web | Candid Sourcing says:

February 23, 2016 at 7:58 am

[…] Use a mouse hovering technique to create an interactive chart […]

Reply
Best of Excel on the Web – Candid Sourcing says:

September 4, 2016 at 7:48 am

[…] Use a mouse hovering technique to create an interactive chart […]

Reply
bila says:

August 16, 2018 at 11:27 am

hello,
i have a question and i was wonder if you could help me please:
i have a map and i want to display some informations ( in front
of the shape or in the label ) when my mouse is over a shape 1 for
example and that information comes from a table on the sheet
thanks

Reply
bhaskar reddy says:

December 27, 2018 at 6:44 pm

Hi

i want to note down a value which is dynamically changing for every 15 minutes.
can you help me on that.

for example:
stock price is ticking every second and i want to note down values of a that stock every 15 minutes.

thanks

Reply

Tom says:

August 16, 2020 at 6:41 pm

Hi Oscar!

Thanks for this excellent post. In regards to your "Final notes" section:

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

Would this not work as well:

Worksheets("Sheet1").AutoFilterMode = False   'I use this in Excel 2007 and 2013

Elsa Smith says:

May 18, 2023 at 12:09 pm

Could you please help me understand how to make the hover area cover the entire cell, rather than just the text, when I add extra cells for reporting? Clicking on different options hasn't worked.

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use mouse hover on a worksheet [VBA]

How to use the user defined function

Excel defined Table

Hide value in cell I11

Formulas

Explaining formula in cell I8

Conditional Formatting

VBA code

Where to put the code?

Final notes

Get the Excel file

Built-in Charts

Combo Charts

Chart elements

Chart basics

Custom charts

Sparklines

Highlight chart elements

Interactive charts

Animate

Advanced charts

Stock charts

Excel categories

26 Responses to “How to use mouse hover on a worksheet [VBA]”

Leave a Reply

How to comment