+'&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.