“Rollover B8 OV1” (like Rollover Beethoven…? get it?)
Update 17 July 2011: This blog entry was featured in Chandoo’s latest post. So if you’re coming here from his site — welcome!
Update 17 July 2011: This blog entry was featured in Chandoo’s latest post. So if you’re coming here from his site — welcome!
original post:
This post was inspired by Chandoo’s post on hyperlinks in Excel. If you haven’t checked his website out yet – you should. It’s excellent.
In this post, I suggest a method for creating a “Rollover” effect for your mouse on an Excel spreadsheet. Rollover techniques (also called “Mouseovers”) are useful to (1) display quick information to the user; (2) to execute a function when a user puts their mouse over a specific region; and (3) to make neat graphical effects.
For item 1, my technique isn’t really necessary. If you want to display brief information to the user when he or she rolls over a cell, the best way to do this is to insert a comment.
But comments are pretty limited. You can only write so much information in a comment before it becomes too cluttered to be useful. Moreover, comments are static. What if you want the information concerning the “target” cell (that is, the cell to be rolled over) to change based on different items in your spreadsheet? We can use the in-cell Hyperlink() formula to accomplish this.
The Hyperlink() formula looks like this:
=Hyperlink(location, [Friendly Text])location - Here you write the intended address of your hyperlink. This can be a webpage (i.e. "http://www.google.com") or a file on your computer.[Friendly Text] - This is an optional field that provides a caption to your hyperlink. If you leave it blank, it will simply display the address in the previous parameter.
To begin our example, you’ll need to create a User Defined Function (UDF) to be executed when the rollover event is initiated. So go into the Visual Basic Environment, create or find a free module and create a UDF that looks something like this.
Public Function MyMouseOverEvent()Sheet1.Range("A1").Value = "Event Fired!"End Function
I can understand if you’re puzzled at this point. UDFs are not really allowed to change values in other cells. However, since we’ll be calling this function from within the Hyperlink() formula, you’ll see this limitation no longer exists.
So now go to Sheet1 and click on cell A2. In that cell, type in the following formula:
=HYPERLINK(MyMouseOverEvent(),"Click here")
Upon hitting Enter, you should get a #VALUE! error, but ignore it for a second. Roll your mouse over cell A2 – and viola! – Cell A1 should now say “Event Fired!”
But what about that pesky #VALUE! error? Remember the limitation that UDFs can’t change other cells? Well, even though Excel let’s you change a value from within your UDF, it still recognizes that it wasn’t supposed to! To get rid of that error is pretty simple. Just rewrite the function in your cell as follows:
=IFERROR(HYPERLINK(MyMouseOverEvent(),"Click here"), "Click here")
And that’s it. If you like this technique and find some cool uses for it, drop me an email and show me.
Here’s some graphical fun I created using this technique. Putting the mouse inside the black rectangle creates a fading snake-like pattern based on your mouse movements. Check out the screen capture:
Make sure to check out my latest (and greatest) example of this technique, here:
Jordan Goldmeier is an accomplished data professional with a wealth of experience across various industries. He currently serves as a consultant at Anarchy Data, where he assists businesses in maximizing the capabilities of Excel for financial planning and analysis. Jordan is also an instructor at Full Stack Modeller and a former Adjunct Instructor in Analytics at Wake Forest University. His extensive career has seen him hold positions as the Chief Operations Officer at Excel.TV, Data Science Manager at DataKind, Data Scientist at Dealer Tire and EY, Analytics & Data Vis Developer at The Perduco Group, and Operations Research Analyst at Booz Allen Hamilton. Jordan's background in data analytics and his passion for Excel make him a valuable resource for businesses seeking to improve their data-driven decision-making processes.
Latest posts by Jordan Goldmeier (see all)
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
<3 snakey
I love the title of this post.
Hi there, thanks for the tip. Unfortunately your snake trails demo link does not work.
@Arvindra Sehmi, I can email it to you if you'd like.
What is needed to make this work in Excel 2003?
very nice!regardsr
It's amazing this trick
Thanks! If you really like this trick then "share the love" as Chandoo would say.
Jason, this is amazing. I've been meaning to spend more time on your blog and I'm glad I did. It took me a while to figure out what was going on here, in fact I went to Chandoo's post to confirm it, and that's that just hovering over the cell runs the function. It probably seems clear that's what's going on, but I spent some time looking for another magic function that was making it all work.I wonder why the hyperlink formula works like that, e.g., just rolling over runs the function. Is it doing some kind of pre-processing that makes its more normal operation work?Anyways, thanks again, this is great.
Thanks Doug! In the Excel Rollovers FAQ I make an uneducated guess about why the rollover works. Make sure to check out the FAQ if you are looking for other work on rollovers. Want to see how weird this thing gets? Try placing that hyperlink formula into something other than IFERROR. Try, for example, =LEN(HYPERLINK(….)). Not only does this way work too, it seems to fire the rollover event much more rapidly than placing it into IFERROR.
This comment has been removed by the author.
I am a newcomer here and am liking what I see, thanks! I was playing around with your rollover as shown but couldn't get it to work with a simple line of code to switch sheets: "ActiveSheet.Next.Select". Any ideas?
Looks like you may have stumbled upon a heretofore unknown limitation. For what it's worth – Congratulations! That said, I've never tried selecting another sheet by using the rollover technique; remember, the technique works like a mouseover event, so the user may think you're playing a cruel trick by sending him or her to another sheet all of a sudden. Then again, a cruel joke on the user is sometimes very funny. If I were you, I might try to recreate some of the work by blogger's Robert Mundigl (ClearlyAndSimply.com) and Chandoo (Chandoo.org). You can find links to them in the FAQ.
Windows only, doesn't work on the mac in Excel 2011
This makes sense since VBA was not included in the most recent versions of Excel for mac
Microsoft replaced VBA on early versions of excel for the mac with AppleScript,In Excel 2011 for Mac, Microsoft has re-introduced VBA, and it's even finally the same version of VBA that Excel for Windows has been using all along. However, there are a substantial number of differences in how Excel itself behaves between the two platforms, and in how VBA interacts with these platforms.You may find this link of help http://www.rondebruin.nl/mac.htm
It's really a nice tutorial I must say. I've been looking for this for almost a week and now I found it.However, I just need some more help. I already create a mouse over effect, but can I remove the effect when I get my mouse off? Any ideas?
Thanks alot for this!! It's amazing. However, I'm having problems with highlighting the cells.Since my cell already has the hyperlink to show text below, I cannot add another hyperlink to highlight cells. Any ideas on how to solve this??
Sorry it took you so long to find, but I'm sure glad you're here! the best way to "remove" an effect is to create another series of hotspots bordering the original. when the mouse exists (by moving over the cells that surround it), the effect can be told to be removed by the surrounding hotspots. depending upon what you're building, this workaround might be complicated. in some instances, I like when the effect stays because I want the user to know over which cell their mouse last hovered.
I may need more information about what you're asking to do. If you want to create another hyperlink effect in another area of a spreadsheet, you can create an additional UDF rollover in your module and point a new series of cells to the new UDF.
Good to know! I would love if someone on a mac could try the rollover examples available on this site.
Thank you very much for your helpful reply. This may be a little complicated but it really solves my problem ;).Wish you a nice day 😉
Hi Jordan thanks alot for your reply! yes my current cell has the UDF and hypderlink for =HYPERLINK(MyMouseOverEvent(),"Click here")I would also like for it to be highlighted in blue upon hovering over. I'm not sure of what I should do in this case.Thank you so much
The metro menu boilerplate is a Workbook boilerplate file that demonstrates my modified version of the Hyperlink ''Rollover'' technique by Jordan Goldmeier…I have taken the concept and modified how the HYPERLINK behaves to: i. only fire once using a ''nested'' HYPERLINK Formula that uses both Function and Cell Referencing. It also uses a Custom Cell Format & the original Conditional Formatting as posted by Jordan ii. allow you to Select Cells or Run Code from any of the links – not just changing Cell Values, but actually firing off thereby exhibiting a somewhat more ''normal'' link behaviour You can link from the Hyperlinks in a number of ways by: Named Range Direct Cell navigation By capturing the Target.Text of the Menu Item to run VBA CodeYou can read more information & download the metro menu boilerplate here:http://www.excelboilerplate.co.uk/boilerplate_documentation/index.htmlThank you for a truly inspiring technique, Mark…
Hi Jordan,hope you can help me. It worked for me, but unfortunately, my boss now wants me to do another view that has all the graphs in one. Basically, do a trending graph with all the variables I used in the individual roll-overs. Is it possible to do that? Many thanks, Dibs
I think it's possible… but I'm not entirely sure I understand your question :). If you're looking to add a second rollover, simply add another User Defined Function (ie MyRollover2()) and have another set of hyperlinks refer to that. You can do for as many rollovers that you might need. I hope I helped answer your question. If not, feel free to reach out to me: jpo645 at gmail.
Thanks Jordan. Will send you an email now.
Just sent you an email. Many thanks
Thanks for sharing Jordan 🙂
Jordan, extraordinary coding my friend! I'm trying to adjust your code to my needs, hopefully I'll manage. If not, I'll come for your help.Once again, amazing! Many thanks
Huge thanks Jordan! This is seriously the greatest improvement for my graphical editing tools. Having a single tracking rectangle over a whole viewport to track clicks and a single – copyable – formula to automatically send X and Y to cells is something I had already given up on. It's too good to be true! Dynamic mouseover tooltips, setting values to cells by clicking on them, … The sky is the limit here.My hat's off to you!
Thanks! the sky is the limit!
This is totally crazy. Thanks!
I’ve created a dashboard which consist of five different sheets. Currently each sheet contains hyperlink formula on which user clicks and move to the desired sheet.
I want instead of clicking on each cell user just hover their mouse on the cell location and it points to the selected location or sheet reference. I tried the below method but it didn’t work properly.
VBA Code:
Public Function Onselect()
Sheets(2).Range(“A2″).Select
End Function
Excel Hyperlink Formula:
=IFERROR(HYPERLINK(Onselect(),”Click Here”),”Click Here”)