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:
Advertisements

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

  1. Doug Glancy

    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.

    Reply
  2. Jordan Goldmeier

    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.

    Reply
  3. Trewism

    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?

    Reply
  4. Jordan Goldmeier

    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.

    Reply
  5. Anonymous

    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

    Reply
  6. Anonymous

    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?

    Reply
  7. ellette

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

    Reply
  8. Jordan Goldmeier

    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.

    Reply
  9. Jordan Goldmeier

    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.

    Reply
  10. ellette

    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

    Reply
  11. Kubiszyn

    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…

    Reply
  12. Dibs

    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

    Reply
  13. Jordan Goldmeier

    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.

    Reply
  14. Daniel Cunha

    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

    Reply
  15. Joonatan Vuorinen

    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!

    Reply
  16. Manish Chopra

    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”)

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s