How to use mouse hover on a worksheet [VBA]
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:
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:
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.
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
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
Excel charts tips and tricks
Custom charts
How to build an arrow chartAdvanced Excel Chart Techniques
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chart no vbaHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a data series in a chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartHow to improve your Excel Chart
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
Excel categories
26 Responses to “How to use mouse hover on a worksheet [VBA]”
Leave a 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.
Hey - thanks for the mention!
This site is a great resource, too.
This is really neat!
I have so many ideas but have so much to learn.
This is a great site!
Jordan and Gerald,
Thanks!
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
Oscar, how do you get the mouse over event to kick in?
duh..........sorry I figured that out, but thanks for this cool tip!
Jordan,
Thanks!
chrisham,
I forgot to mention that! Conditional formatting compares the value in cell I11.
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
Tutorial Please
Is it posible to create this interactive chart into interactive candlestick chart to compare two or more chart ?
Rudy,
Yes, I believe so. Hopefully I´ll have a tutorial ready this week.
[...] Rudy asks in this post Use a mouse hovering technique to create an interactive chart: [...]
[…] Tutorial on Interactive Dashboard using Hyperlinks by Chandoo Use a mouse hovering technique to create an interactive chart by Oscar of […]
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
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.
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
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
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.
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
[…] Use a mouse hovering technique to create an interactive chart […]
[…] Use a mouse hovering technique to create an interactive chart […]
[…] Use a mouse hovering technique to create an interactive chart […]
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
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
Hi Oscar!
Thanks for this excellent post. In regards to your "Final notes" section:
Would this not work as well:
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.