Why Does Excel Make It So Hard?

I had a simple requirement recently, I wanted to create a form where it was possible to navigate around using shortcut keys. Easy you would think, just assign accelerator keys to labels and/or commandbuttons … problem solved.

Unfortunately, I also have a number of frames with multiple checkboxes in them, groups of
checkboxes. And guess what, frames don’t support the accelerator property.

My first thought was to overlay the frame with a label and blank out the frame caption. As labels have an accelerator property, this should work, using a shortcut key would hotkey to the label and then you tab through the checkboxes within the frame. Unfortunately, I couldn’t blank out the frame caption because the top of the frame is taken from the top of the caption, when there isn’t one it gets shunted up 6 points, so the label has to be precisely positioned to avoid blurring. I
managed to get this approach to work as long as the overlaid label has a TabIndex of 0,
and the first (real) control with a TabIndex of 1, forscing that control to take focus on hotkeying.

So, there you are, a bit of creative thought and the problem was solved, albeit a tad kludgy and inelegant.

Of course, life is never that simple, certainly not with computers and certainly not with Excel. I have since come across a problem
with this approach. These frames are on a single page of a
multipage control, and if it is switched to another page and then back again, some of the underlined characters are losing their underline.
Not all, just some.

Presumably the frame caption has come forward over the label caption, but how
and why? What seems to be happening is that the label doesn’t overlay the frame caption as I had hoped, but the top half gets hidden by the caption, the bottom half still shows. Thus, the underlined character that is the accelerator key does show, or at least it does on first showing. I still have no idea why some disappear when showing another page and returning.

This is a picture of part of the form, showing how it looks on startup

I was stuck, I couldn’t think of a solution, so at this point I decided to call on the help of friends and colleagues in the Excel community.

Two Excel MVPs offered their help. Peter Thornton suggested an approach that was intrinsically the same as mine, although he used a nice method of using a commandbutton to accept the actual accelerator key, and then redirect the focus to the first checkbox within that frame. This was a technique that I had employed on another page of the multipage to have a shortcut key to a listbox, so why I hadn’t thought of it here as well is a bit odd, but although I could not demonstrate on Peter’s example file that it could also lose the underlines, as it was essentially the same as my solution I felt that it would probably suffer the same fate.

When I first saw the other solution, I thought that it too was a similar approach, although I was at first confused as to how this approach was managing to get the label to properly overlay the frame caption, and how it had the frame caption blanked out without the effects mine had suffered in this way. Further investigation showed it was a lot cuter than this. Here are the essentials of what had been done:

– rather than using a frame to contain the checkboxes, there is a label, sized to encompass all of the original frame’s controls. This label’s SpecialEffect property was set to fmSpecialEffectEtched and the BackStyle property to fmBackStyleTransparent, to make it look like a frame. The label caption was blank

– a standard label was overlaid onto this label, this being the label for which the accelerator key was targettedr, and as before, this was given a TabIndex of 0,
and the first (real) control with a TabIndex of 1, forcing the first real control to take focus when the shortcut is used

– finally, both of these labels, and all of the encompassed controls were encapsulated in a frame, to keep the grouping aspect required, but this frame’s SpecialEffect property was set to fmSpecialEffectFlat, and its caption to blank, so that it didn’t show.

As can be seen from this image, the effect is exactly as required,

but because the target label is not half-buried at the top of a frame, it doesn’t suffer the loss of the underline when navigating through the pages. The invisible frame(s) can be positioned so that they overlap a little, in order to reduce the gap between them.

I have to thank Andy Pope for this really creative solution. Nice one Andy! And thanks again to Peter, an Excel MVP who is interested in the atypical Excel capability.

But I have to ask, why is it so difficult to do something so simple?

 

For anyone interested, an example file of this technique is attached in the RSS feed.