+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
Oh, there is the little trick about inserting a checkmark symbol - but this is not dynamic and provides zero sizzle.
Then there are the form controls. While they have always had potential, I think they have not been enhanced in over a decade. They are cumbersome and create potential problems when rows are resized or moved, even though they come with options that help a little in this regard.
But perhaps the biggest shortcoming of form control checkboxes is the fact that they do not automatically clone themselves in a table as the table grows. It's astounding to me that after all these years, Excel does not have a Check Column as an option for a table.
I needed this recently and so I created my own. Excel 2007 and 2010 come with a new sub-feature of Conditional Formatting called Icon Sets. With a little VBA coding to create a generic round-robin array-element selector, I came up with a nice addition to the Excel interface. The animated GIF above shows an inkling of what can be done with this technique.
I think it is pretty cool, what do you think?
Here is the file:
The round-robin functionality will work in older versions of Excel (which can be very useful in its own right - examine the Alpha Round Robin Demo in the workbook for an example), but the Icons Sets will display simply as numbers in the old versions.
Learn Advanced Excel!
Advanced Interface Design is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning.
I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.
This Spring I will be offering the course again. There's already over 200 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!
If you liked this article, please share it!
Very nice programming. It took me a long while to step through all the code. Got stuck with the IF values AND Len THEN statement, but finally realized that an Empty range value is equal to zero and "".
I learned several new VBA tricks and liked how you used the TRANSPOSE function to get the alphabet.
Thanks for all the work you put into this worksheet. Great stuff!
Very very nice. Looks great and the code is beautiful.
thnx
I am Bob from the gold coast home builders site, and I think you are brilliant here. You were able to discuss here the clarity of the subject. Very useful information.Thank you.
This works very well in your file, but when I try to incorporate it into my projects, it fails. I wish we had some basic instructions about how to use it in another file. I have coped the sheets, the module, and the VBA code into the new workbook, but, alas, the double-click refuses to work for me.
Hi winm.
Make sure that you are including the "Worksheet_BeforeDoubleClick()" subroutine from the worksheet module in VBA, not just the routines in Module1.
If you cannot get it going, please send me your file and I'll take a look.
Regards,
Daniel
mvp@excelhero.com
Hi Daniel:
Thank you for your suggestion. I found that I needed to change the parameters of the named range to get it to work. After pointing the name to the right place in my workbook, it worked fine.
Thank you for this great resource!
Regards,
Win
win@winthropmorgan.com