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.
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!
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.
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!
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.
ReplyDeleteThanks 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.
ReplyDeleteMan this is sick!! Really the coolest thing I've ever seen in Excel! Great job getting everything to work!
ReplyDeleteNice work! This is fantastico :-)
ReplyDeleteStill clicking and hovering around in total amazement!
ReplyDeleteThis VBA starter here just got a reminder he's still got a long, looong way to go. :p
Thanks - I'm glad you enjoyed it!
DeleteEveryone enjoyed it, I'm sure!
DeleteReally, 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.
ReplyDeleteThanks
Al
Sorry, I never really completed the tutorial for this. However, you might find these tutorials helpful:
Deletehttp://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 :)
Jordan,
ReplyDeleteCongrats on a truly remarkable work ..!!!
A great inspiration for many of us ...
James
Man I really feel your joy with hoover effect.
ReplyDeleteI 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.
Hi,
ReplyDeleteThanks 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?
HaHa, I need to update my previous post!
DeleteIn 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!
Thanks for your enthusiasm! Several of your questions are addressed in the faq:
Deletehttp://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
Oh, also, this works just like your example but with no helpers, CF or shapes...
ReplyDeletePublic 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
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?
ReplyDeleteYes, 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.
Deletethe link is broken :( How can I download this file ?
ReplyDeleteThe download is no longer available?
ReplyDeleteThanks 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.
ReplyDeleteBro 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!
ReplyDeleteI'll definitely be studying this! and it's aesthetics are beautiful! LOVE IT! Great share!
Thanks man!
DeleteAbout the table of eleements. http://hfilipen-talentedchildren.blogspot.com
ReplyDeleteAbout the table of eleements. http://hfilipen-talentedchildren.blogspot.com
ReplyDeleteHi Jordan,
ReplyDeleteVery 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
Seems like the link to the file is dead...
ReplyDeleteLink is dead!! :(
ReplyDeleteLogging 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.
ReplyDeleteCustomerservicenumber.ca
ReplyDeleteThanks 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
ReplyDeleteYou have a 918kiss download apk iphone great blog - I would think your readership is very high
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
ReplyDeletetogel online
ReplyDeletebandar togel terpercaya
agen togel
judi togel
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.
ReplyDelete162.214.161.101
162.241.130.230
This comment has been removed by the author.
Deletevery nice article. Also visit my website http://139.59.77.247/
ReplyDeleteNice 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
ReplyDeleteplease keep it up!
main dan menangkan puluhan juta setiap harinya di situs judi online https://www.fireball20xl.com/
ReplyDeleteThank you for sharing your info. I certainly love this site.
ReplyDeletebetmatik
ReplyDeletekralbet
betpark
tipobet
slot siteleri
kibris bahis siteleri
poker siteleri
bonus veren siteler
mobil ödeme bahis
MİJ
شركة تسليك مجاري بالدمام UmmxyLdMFb
ReplyDeleteشركة تنظيف مكيفات بالاحساء B3sR7jIrY6
ReplyDeleteشركة تسليك مجاري بالجبيل OWTktC4SQc
ReplyDelete