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 click 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".
- Click on "Table" button.
- Click 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".
- Click Category: Custom to select it.
- Type ;;; (see image above).
- Click 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
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
25 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 clicked, 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 download .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 downloaded the file but code is not appearing to find this?
thank you
CasesST,
I've downloaded the file but code is not appearing to find this?
1. Open the VB Editor (Press Alt+F11).
2. Double click Modules in the Project Explorer window.
3. Double click 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: