Everyone! We've moved to WordPress.

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

"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!

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:

Comments: 47

  1. I love the title of this post.

    ReplyDelete
  2. Hi there, thanks for the tip. Unfortunately your snake trails demo link does not work.

    ReplyDelete
  3. @Arvindra Sehmi, I can email it to you if you'd like.

    ReplyDelete
  4. What is needed to make this work in Excel 2003?

    ReplyDelete
  5. Replies
    1. Thanks! If you really like this trick then "share the love" as Chandoo would say.

      Delete
  6. 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.

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. This comment has been removed by the author.

      Delete
  7. 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?

    ReplyDelete
    Replies
    1. 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.

      Delete
  8. Windows only, doesn't work on the mac in Excel 2011

    ReplyDelete
    Replies
    1. This makes sense since VBA was not included in the most recent versions of Excel for mac

      Delete
  9. 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

    ReplyDelete
    Replies
    1. Good to know! I would love if someone on a mac could try the rollover examples available on this site.

      Delete
  10. 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?

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. 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 ;)

      Delete
  11. 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??

    ReplyDelete
    Replies
    1. 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.

      Delete
  12. 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

    ReplyDelete
  13. 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 Code

    You can read more information & download the metro menu boilerplate here:
    http://www.excelboilerplate.co.uk/boilerplate_documentation/index.html

    Thank you for a truly inspiring technique, Mark...

    ReplyDelete
  14. 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

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. Thanks Jordan. Will send you an email now.

      Delete
    3. Just sent you an email. Many thanks

      Delete
  15. 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

    ReplyDelete
  16. 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!

    ReplyDelete
  17. Jordan -

    First and foremost, thank you for this! It has been a great help. I wanted to ask if it is possible to have excel revert back to the original cell values if we "unhover"? Essentially, I guess I am asking whether or not you believe it is possible to hover and "preview" and when we move the mouse away from the "hotspot" the original values will return.

    Thanks in advance for your time!

    ReplyDelete
    Replies
    1. There are a few ways to do an unhover. The easiest way is to create a "moat" of hotspots around the original hotspot section that reinstates old values. This isn't a perfect solution since sometimes your mouse moves to quickly over the moat and the unhover mechanism isn't executed. Another method, one I've employed on more than one occasion, is to use the Timer API to wait a second after your mouse moves away from the hotspot to reinstate old values (alternatively, you could set this timer to work for a fraction of a second and make it appear instantaneous). If you'd like a file demonstrating the latter method, feel free to reach out to me over email and I'll send it your way.

      Delete
    2. Just sent over and email. Thanks again Jordan. This is great stuff!

      Delete
    3. Looks like I got the wrong email. May you please let me know the address I can email you at?

      Delete
    4. Jordan,

      Great post, endless possibilities. I wanted to use the UDF to hide/show a column (column A) which contained a bunch of macro buttons, by hovering over a hyperlink in cell B1 - to mimic a user control panel. Unfortunately hiding a row or column does not seem to work with the hover over technique. Have you found this limitation too? Great function nevertheless, thanks. Rico.

      Delete
    5. rico - send an email to me to my gmail address listed in the post above - I'll send you what I have on this

      Delete
    6. Thanks, email sent... lightening reply!

      Delete
  18. Thanks, this is great. Any way you can do the mouse over action over a shape object?

    ReplyDelete
    Replies
    1. Not directly. What you need to do is place the shape in front of the rollover cell. See these examples:

      http://www.clearlyandsimply.com/clearly_and_simply/2012/11/roll-over-tooltips-and-web-actions-on-a-microsoft-excel-dashboard.html

      http://optionexplicitvba.blogspot.com/2012/10/interactive-united-states-2012.html

      and, of course,

      http://optionexplicitvba.com/2012/09/17/the-excel-rollover-mini-faq/

      Delete
    2. Thanks for the quick reply and help. Much appreciated.

      Delete
  19. Nice and useful code. I actually need the input being erased again when my pointer is not in the particular cell anymore. Anyone a solution for this?

    ReplyDelete
    Replies
    1. I sure do! I present three methods on how to do this on my new blog: http://optionexplicitvba.com/2013/09/13/handling-excel-rollover-popups/

      Delete
  20. This VBA is too admirable , the loop is working finest in my opinion you done a quietly great work.truly thank you so much for this great code.Get free dashboard software.

    ReplyDelete
  21. Thank you for sharing this helpful article on VBA. It is very informative. Cheers!

    ReplyDelete