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

  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. Nice work! This is fantastico :-)

    ReplyDelete
  5. 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
  6. 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
  7. Jordan,

    Congrats on a truly remarkable work ..!!!

    A great inspiration for many of us ...

    James

    ReplyDelete
  8. 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
  9. 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
  10. 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
  11. 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
  12. the link is broken :( How can I download this file ?

    ReplyDelete
  13. The download is no longer available?

    ReplyDelete
  14. Thanks for letting me know the download link is no longer available. I have reached out to my hosting provider to find out what's going on. If you send me a personal email: jpo645 at g mail, I can deliver the link to you personally.

    ReplyDelete
  15. Bro this is DOPE!! On a study break thinking about how this table could be so much better with a little VBA magic and thought a hover would be cool and came across your blog!! Great work!

    I'll definitely be studying this! and it's aesthetics are beautiful! LOVE IT! Great share!

    ReplyDelete
  16. About the table of eleements. http://hfilipen-talentedchildren.blogspot.com

    ReplyDelete
  17. About the table of eleements. http://hfilipen-talentedchildren.blogspot.com

    ReplyDelete
  18. Hi Jordan,

    Very impressive work - love the concept and the execution is absolutely superb :-)

    I read in one of your other comments that you like you incorporate the rollover with the ability to click cells (http://optionexplicitvba.blogspot.co.uk/2012/06/how-to-highlighting-cells-using.html. Do you have an example ?

    I am wanting to incorporate a 'click cell' into your periodic table. When the cell is clicked, it directs you to a url or tab within the workbook.

    Any pointers appreciated :-)

    Thanks

    ReplyDelete
  19. Seems like the link to the file is dead...

    ReplyDelete
  20. Logging in online gambling games is not a stranger now. various kinds of online gambling games that can make players still want to play. Even more days the number of gambling players is increasing. This is because indeed gambling games are already very interesting to try and play during leisure time. But at this time along with the development of the era of gambling the game was not only played by betting directly on the casinos.

    ReplyDelete
  21. Thanks for the post. Im a big scr888 apk fan of the blog, i've even put a little bookmark right on the tool bar of my Firefox you'll be happy to find out!

    ReplyDelete

  22. You have a 918kiss download apk iphone great blog - I would think your readership is very high

    ReplyDelete
  23. Wow, what a blog! I mean, you just have so much guts to go ahead and tell it like it is. Youre what blogging needs, an open minded superhero who isnt afraid 168 8099 apk scr888 casino game 4 to tell it like it is. This is definitely something people need to be up on. Good luck in the future, man

    ReplyDelete
  24. Very good article, I often read articles on topics like this but never this good. Don't forget to read our article which is certainly no less interesting.

    162.214.161.101
    162.241.130.230

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  25. Nice post, truly insightful info about SEO. I have used these techniques to bring more traffic to the site and it worked and I used tools to do backlinking. SEO techniques
    please keep it up!

    ReplyDelete
  26. main dan menangkan puluhan juta setiap harinya di situs judi online https://www.fireball20xl.com/

    ReplyDelete
  27. Thank you for sharing your info. I certainly love this site.

    ReplyDelete