Everyone! We've moved to WordPress.

Interactive Periodic Table of Elements in Excel

I've been on a real rollover kick lately. I'm really trying to figure out if it can be useful. Earlier today I started making a Periodic Table of Elements using Excel. I employed the rollover technique to allow the user to gain information about an element simply by rolling over a cell. Well, for some reason, I couldn't stop there. So what was meant to be a small project ballooned into something larger. Unfortunately, my sticking to good coding practice didn't keep up with craving to do more. So what I present to you below isn't really a polished product. If you poke through the named ranges and the rollover indexes, you'll probably see that I add and subtract one to them somewhat randomly (a cheap trick - this is  due to my trying to reconcile the table copied from Wikipedia with my indices).

As you can see below, you can not only gain information about an element but you can also toggle on and off different element groupings.

Unable to display content. Adobe Flash is required.

If you want to "crack" the file, the first thing you'll need to do is reset the ScrollArea (Click on a cell. Go to the Developer tab, click Properties. Delete the reference in the ScrollArea box.). Then just unhide everything.

Good luck.
Periodic Table.xlsm

Update -
Reader Dario found an error in the spreadsheet (see the comments) - this is the result of some carelessness and cheap tricks on my part. An updated version will be released tonight. In the meantime however, you can still poke around the file :).

Another Update -
I've since fixed the bug described in the reader comments. If you find anything else, let me know!

Comments: 16

  1. Is there a bug or is it just a functionality or lack thereof that if you hover over the cells from left to right that it skips one. From right to left it doesn't skip any.

    ReplyDelete
  2. Thanks for pointing that out to me. That is indeed an error - the result of my using cheap tricks instead of good programming practices. I've since fixed the problem - however, I am not able to upload the new file while at work. Tonight the new file will be uploaded in its place.

    ReplyDelete
  3. Man this is sick!! Really the coolest thing I've ever seen in Excel! Great job getting everything to work!

    ReplyDelete
  4. Still clicking and hovering around in total amazement!
    This VBA starter here just got a reminder he's still got a long, looong way to go. :p

    ReplyDelete
  5. Really, really nice. Is there an explanation anywhere on what you did. How would I go about modifying the overall structure, if I wanted to highlight different portions of a worksheet, nothing in particular in mind at this point.
    Thanks
    Al

    ReplyDelete
    Replies
    1. Sorry, I never really completed the tutorial for this. However, you might find these tutorials helpful:

      http://optionexplicitvba.blogspot.com/2012/06/how-to-highlighting-cells-using.html
      http://optionexplicitvba.blogspot.com/2012/09/handling-rollover-clicks-without-using.html
      http://www.clearlyandsimply.com/clearly_and_simply/2012/11/roll-over-tooltips-and-web-actions-on-a-microsoft-excel-dashboard.html

      As well, you can go snooping through the file - I'm available by email to answer specific questions :)

      Delete
  6. Jordan,

    Congrats on a truly remarkable work ..!!!

    A great inspiration for many of us ...

    James

    ReplyDelete
  7. Man I really feel your joy with hoover effect.
    I was looking so long for it. I've always wanted to make like interactive calanders, or visual effects, or anything that would surprise the audience.

    I found out today, and I'm definitly gonna have a lot of fun with it.
    I wish excel would create a friendly version of it, because it sucks having to program with hacked technices. Once things fail you get a lot of headache, so congratz on your achievments, I know it must be hard.

    ReplyDelete
  8. Hi,
    Thanks for this, its a great little trick!

    I found a few things about the boundaries of this technique and I wonder if you can confirm the following?

    For brevity, I define a "RUDF" as a function called by a hyperlink function...

    1. Can an object's properties be changed by a RUDF? YEP
    2. Can an object's Method be activated by a RUDF? NOPE
    3. Is vba error trapping active inside RUDF's? NO, you have to do your own.
    4. Can you use Static declarations inside RUDF's? YEP
    5. Can you switch off the Hyperlink events with Application.EnableEvents=False? NOPE
    6. Can you call another sub from inside a RUDF? YEP
    7. Do Worksheet events, like Change, fire when a cell value is changed by a RUDF? YEP
    8. Can a RUDF or any sub called by a RUDF execute a method of an object? NOPE.
    9. Can a change event, triggered by a a RUDF action, execute a method of any object? NOPE

    From what I can see, there is no way to cause a shape under the mouse to be activated using this technique.

    Have you found the same behaviour?
    I'm working in Excel 2013.

    What I'd really like to do is Activate the chart under the mouse to expose its mouse events, without having to click on the chart first?
    Any suggestions?

    ReplyDelete
    Replies
    1. HaHa, I need to update my previous post!

      In fact you can activate chart objects with this!!
      Fantastic :))

      I thought I would test it on range objects first because it would be simpler.
      Big mistake. haha

      The above list is true for range objects anyway.

      Thanks again for the cute trick!

      Delete
    2. Thanks for your enthusiasm! Several of your questions are addressed in the faq:
      http://optionexplicitvba.wordpress.com/2012/09/17/the-excel-rollover-mini-faq/

      As well, to see other applications:
      http://optionexplicitvba.blogspot.com/2012/08/details-on-demand-bring-up-details-on.html
      http://optionexplicitvba.blogspot.com/2012/11/cube-rollover-method-returns.html
      http://optionexplicitvba.wordpress.com/2012/09/20/handling-rollover-clicks-without-using-the-worksheet_selectionchange-event/

      Sorry for intermingling wordpress and blogspot links. I've actually ported everything over to wordpress - this blog remains as a legacy construct. Too many links to it - so I didn't want to get rid of it. The blogspot links above contain the correct videos animations - the wordpress versions didn't correctly port them over. If you have follow up questions/thoughts/comments feel free to post them optionexplicitvba.wordpress.com.

      Thanks
      Jordan

      Delete
  9. Oh, also, this works just like your example but with no helpers, CF or shapes...

    Public Function Rollover(row As Long, col As Long)
    Const targetOffset = 0
    Dim c As Range
    Static prevCell As Range, prevElement As Integer

    Set c = Cells(row, col)
    If Intersect(c, prevCell) Is Nothing Then
    If Not prevCell Is Nothing Then
    prevCell.Offset(0, targetOffset).Interior.ColorIndex = prevElement
    End If
    Set prevCell = c
    prevElement = c.Offset(0, targetOffset).Interior.ColorIndex
    c.Offset(0, targetOffset).Interior.ColorIndex = 1
    End If
    End Function

    ReplyDelete
  10. Here's a really simple question - one which I feel foolish even asking; On the 'Table' worksheet, how did you block out all cells outside of the range "B1:Y14" ? And also, you've prevented users from actually selecting any of the elements - I assume you've locked those cells. Is that working as designed?

    ReplyDelete
    Replies
    1. Yes, it is working as designed. I "locked" these cells out by defining the ScrollArea property of the sheet. Take a look at this article: http://chandoo.org/wp/2011/01/25/worksheet-properties-via-a-menu/ and see the subsection 'Scroll Area.' Let me know if you have any other questions.

      Delete