Improving Sheet Selection
Today's author: Sam Radakovitz, a Program Manager on the Excel team that enjoys creating VBA applications in Excel.
A bit ago I got a question through email asking if we could change the 'grid selection color' in Excel. Or at least I called it 'grid selection color', it's basically what the cells look like when you select a block or multiple blocks of them:
As you can see, the color is a bit light and hard for some folks to see, especially on a laptop if you're looking at it from the wrong angle. Unfortunately, in Excel 2007 you can't really change the color of the selection. You used to be able to change the 'Selected Items' color in the Windows appearance settings but that doesn't work with Excel 2007. This is something we're looking to address in the Excel … but until then I wanted to try and provide an add-in solution. I tried a couple of solutions and I wasn't 100% successful with either attempt, but maybe a code savvy blog reader can pick up where I left off and finish the task.
Approach #1, Shapes
My first approach was to use OfficeArt shapes to create the selection. It's pretty easy to do code wise: you get a selection change event, you loop through each area in the selection and drop a shape on it:
I take the shape and inset it a little bit in the selection so the drag fill is still available, then pick a darker color and make it a bit transparent. I converted this into an add-in and it was good to go … but feature creep sets in and I start thinking about the different things I can do. In short, I added some options and created a ribbon entry point to control them:
I added an option on the menu to turn on/off the selection add-in, a color chooser, transparency control, and some additional information you can display in the selection like the count of cells (different that the count in the status bar btw) and address. So now the selection looks like:
As I was playing around, I found one of my favorite things to do is to make the selection color a random color … not sure why it's so entertaining, but I couldn't stop selecting things after enabling the option:
The final interesting 'feature creep' item is the ability to set a formula and have it evaluated on each selection, then return the results to the text in the selection. It's the "custom formula…" menu item in the picture above. For an example: In the picture below, I create a 'countif' formula counting the number of times 'Sam' is in the selection, the formula I type in is:
="Number of Sams: " & countif([selection],"sam")
The [selection] part is the part replaced with the address of the active selection. The final result:
Overall using a shape is an interesting idea … but it isn't a 100% win for two main reasons … and some other smaller ones, but I'll speak to the ones I think are deal-breakers:
No Undo
Because the selection event fires often, and we are creating a shape in the active sheet, this kills the Undo stack … and people need their Undo stacks! Maybe one day, we'll be able to write code and not blow away the Undo stack, but until then, this is bad bad bad.
No right click
Because it's a shape, I can catch the left click no problem, but the right click isn't catchable, and puts the shape into edit mode. I've altered the left click on the shape to show the right click menu by sending a mouse click to Excel, but that isn't enough. I *thought* I had a work around for this … my plan was to create an ActiveX label control over the shape and make the label transparent, but catch the clicks since the label has a left and right mouse button events … but I couldn't get the label created w/o blowing away my global variables … and then it didn't surface the events as it should. Maybe something I was doing wrong, but if someone gets that working, please let me know!
Approach #2, Userform
My second approach didn't get too far, but I think its superior in that it addresses the two deal-breakers above. My plan was to have an add-in create a modeless and unclick-able form, which would then move with your selection in Excel. Since you can't click it, your mouse commands work great … and we aren't adjust things that cause the UNDO stack to get blow away. I got the form to be unclick-able and transparent, but the big problem was positioning and sizing the form:
The position and size of the shape solution (the first approach) was easy since we're using the grid coordinates to position the shape, and the top/left of a range is the same for the shape. For the form, we're using the screen positioning, so some translation has to happen between the grid position and the form position. There are some functions that are supposed to help with this, but they don't work as I would expect. An Internet search turns up some folks who have figured it out, but it's not trivial at all and at the moment I don't have the time to research it further.
My final words
Both solutions are available for download; see the links at the end of this article. If you're one of the code savvy readers who wants to alter the add-ins, by all means download it and shoot us a mail and tell us about what you did! Also, if you're reading this and have an idea or any comments on the add-ins or Excel grid selection in general, don't hesitate to shoot us a email or post a comment.
