It’s Almost IKSD Eve

You know that this Wednesday is International Keyboard Shortcut Day, right? To start the celebration, I’m reposting a blog from trueinsight.za.com with permission of the author. Enjoy!

This week (Week 26 of 2014, for the Excel aficionados), we have had a very interesting discussion on the Word-PC e-mail listbook on Word, together with many others topics that you might benefit from.

First up, how not to learn keyboard shortcuts: Don’t search for a list and try to commit it to memory. This fails, because firstly we are overloading our brains with too much information in too short a space of time (any student can verify this three weeks after an exam). Secondly, we are dealing with the list as an abstract thing, and not relating it to our use.

My own experience has been that a very simple discipline (which I will get to shortly) has worked very well for learning keyboard shortcuts.

Before that, though, it is useful to examine how we discover keyboard shortcuts. Yes, lists are useful for that—every time I see an article on keyboard shortcuts, I make certain that I read it (I just picked up a new Excel keyboard shortcut this week in that manner). Second, we all (or at least those who type as inaccurately as I do) have had the experience of pressing something on the keyboard, and seeing something go haywire (e.g. we wanted Shift+R for an uppercase R, but hit Ctrl+R and right-aligned our paragraph). Normally we just undo (Ctrl+Z), but I normally take some time out, try to see if I can figure out what I just hit (I will sometimes undo and try to recreate the keyboard shortcut with what I thought I was typing). I will also evaluate whether this keyboard shortcut is useful for me—if not (e.g., the keyboard shortcuts in Word for Danish characters are some that, no offense to the Danes, I hope I never have to use), then I just forget about it and carry on with my life. If it does appear to be useful, then I will set about learning it. Also, it pays to be observant. It amazes me that people look at menus, and never see (i.e., observe) the keyboard shortcuts listed in those menus (of course, since Office 2007, the Office keyboard shortcuts are displayed in better-hidden tooltips—yet another way Microsoft seems intent on de-cluttering by dumbing down).

One other note before I get to the learning bit. Some keyboard shortcuts are system-wide—meaning they will either work on the OS level, or will work for almost all programs known to man (if you will excuse the exaggeration). Examples are Ctrl+C for Copy, Ctrl+P for Print, Ctrl+S for Save, etc.). Some are, for want of a better term, platform-wide (e.g., Ctrl+H for Replace in all of the Microsoft Office programs, while Ctrl+R does the same in many other programs). And some are program-specific (e.g., Ctrl+Alt+M for a comment in Word, which is Shift+F2 in Excel). If I am working in a new program, I will (after having duly saved my work), freely experiment in that program with keyboard shortcuts that I believe would work there (i.e., those keyboard shortcuts that are in the first two categories I mentioned). I’ve never had a computer meltdown as a result of this (yet!), and it gives me, so to speak, more bang for my buck for the effort of learning keyboard shortcuts.

So, how do we learn keyboard shortcuts?

Still not there yet…. Some keyboard shortcuts are mnemonic (Ctrl+B for Bold, Ctrl+I for italics, Ctrl+C for Copy—and, on that point, notice the position of the X,C,V keys on a qwerty keyboard, and remember Cut, Copy, Paste—and, in Word, Ctrl+L for Left alignment, Ctrl+R for Right alignment, and Ctrl+J for Justified alignment, etc.). Unfortunately, not all keyboard shortcuts can be mnemonic (e.g., Ctrl+C is already taken, so Centre alignment in Word becomes Ctrl+E). Obviously, mnemonic keyboard shortcuts are going to be easier to remember, but we need to go further.

So, how do we learn keyboard shortcuts?

Firstly, one or two at a time. This overcomes the first mistake I noted at the start. Sometimes I will do a handful at a time (e.g., when I discovered various commonly used number formats in Excel with Ctrl+Shift+1 (Ctrl+!), Ctrl+Shift+2 (Ctrl+@), Ctrl+Shift+3 (Ctrl+#), Ctrl+Shift+4 (Ctrl+$), Ctrl+Shift+5 (Ctrl+%), Ctrl+Shift+6 (Ctrl+^)—although I never use the last one in my line of work). But when I do teach myself a whole bunch of keyboard shortcuts, there will be some relation between them, as in the list I just showed here.

Secondly, we want to relate it to usage (thus overcoming the second mistake mentioned above). Allow me to explain, because here I am finally coming to that little discipline that I hinted at. For example, this week I learned that Ctrl+6 hides all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: http://www.databison.com/so-how-many-of-these-excel-shortcuts-do-you-know-punk) then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may be two or three weeks before I get an opportunity to use that one. By then, I will have forgotten it. So three weeks from now, most people think to themselves:
“What was that keyboard shortcut for hiding objects again? Arrghh. Can’t remember it. Ah well, that just proves that this keyboard shortcut stuff is a load of junk. I’ll take my mouse, click Home | Find and Select | Selection Pane, and then click on Hide All.”

What I do, in contrast, is use this little discipline: I stop, go back to my list or note, look the keyboard shortcut up, and then use it. This process of using it activates a second memory channel (muscle engrams—go look it up), over and above the cognitive memory process. We all use this, we just don’t think about it. For example, I’m standing in front of the ATM to draw cash, and it’s one of those days where, for the life of me, I can’t remember my PIN number. So what do I do? Hopefully, not pull out the little slip of paper—that everyone can see!—where I scribbled it down. I know people who have had their accounts emptied in this way! No, I make as if I’m going to type the PIN, and suddenly it comes back to me. I am using my muscle engrams (the same things tennis or cricket or any other sport players use to perform those shots so masterfully) to bring back the memory of what my PIN is. In other words, the part of my brain that moves my hand to type my PIN also remembers that action—it is a second memory channel. So when I use that with keyboard shortcuts, I am actually memorising the keyboard shortcut in two different ways (cognitively and “physically”). In fact, even now, I sometimes struggle to recall what a certain keyboard shortcut is, but if I get behind the keyboard and make as if I’m going to do it, I can figure it out again.

Now sure, that process of stopping, looking up the keyboard shortcut, and using it, does take longer than just doing it with the mouse. But it is a short term loss for a long term gain. Once that keyboard shortcut is mastered, I will save a few seconds every time I use it, regaining and overtaking what I have lost in looking it up once or twice.

And one last thing. I know keyboard shortcuts are not for everyone. I must also confess that I am not musophbic (go look it up, although I am twisting the word beyond its original intention), and the mouse has its place—some things really cannot be done without the mouse (some programs, for example, are really not keyboard shortcut friendly) and some things (not many, but they are there) can be done faster with the mouse than the keyboard. Some things, of course, can only be done with the keyboard and mouse in conjunction (macros aside): Did you know that you can select one sentence in Word by holding the Ctrl key while clicking anywhere in that sentence? (This does sometimes trip over abbreviations, though.)

But keyboard shortcuts definitely do help you work faster, and keyboard shortcuts can help out in the most unlikely situations—those that you have never thought of (have you ever tried working with the touchpad of your laptop while seated in a plane going through some turbulence?). So much so that, in addition to keyboard shortcuts, I have memorised quite a few ribbon manipulations on the keyboard (for one example, Alt | JL | F | C to AutoFit a table in Word—I defy you to do it quicker with the mouse than I do with the keyboard. Sure, I could create a keyboard shortcut for that and do it seven split seconds faster, but typing that string is so quick, I hardly see the need.

The question then becomes twofold: So how many keyboard shortcuts do you know, punk? And, more importantly, how many keyboard shortcuts will you know a year from now?

AutoHotKey Update

Have you been wondering what’s up my keyboarding sleeve lately? Too bad, I’m telling you anyway.

Insert Date

You know how pressing Ctrl+; inserts the current date in Excel? Now I can do that in any program. It’s particularly useful when I’m updating an item at goodtodo.com where I date stamp notes.

I’ve already re-purposed Ctrl+semicolon in Excel, so it’s in the section that only works outside of Excel.

Stop Helping Me

The other day I accidentally pressed F1. In Excel. For the last time. I could have just disabled F1, but I thought I’d try something different. This figures out what’s in the active cell and searches for it in Firefox.

Now when I press F1, I no longer get a separate window, stealing the focus, and not being very helpful. Instead I get a Google search that’s slightly more helpful.

I had to upgrade my AHK to use the ComObjActive function. The

is because Firefox is pinned to my Windows 7 taskbar in the second location (# is the Windows key in AHK).

(control+k) moves the focus to the Firefox search textbox. If you are using Chrome, for instance, you’d want Ctrl+d because the address bar and the search bar are the same.

That’s not as nice as if I parsed the function, but it gets the job done. I tried to use the clipboard so that I could edit a cell, select a function, and press F1 to get a Google search for this function. I couldn’t get the clipboard to work reliably, no doubt because I was in edit mode. Hmmm, maybe Ctrl+C, then esc to get out of edit mode? Dangerous.

Seriously, Stop Helping Me

Don’t forget VBA help.

This copies the selection. If what it copies is more than a single character, it searches for that. If it’s one character or less, it selects the current word under the cursor and searches for that. In both cases, it puts “excel vba” before the search term. If you want to change that to

, you won’t hear me complain.

Some VBE Sugar

Type ThisPath and it expands to

Type ppg and it expands to

And Some Problems

If I type acd in a Windows Explorer address bar (or File Open or File Save dialog) it expands to the Accounting\Restricted\ folder on our main network share. That shortcut used to be acr until I figured out how common acr is in English words. The combination acd appears almost never, at least in my admittedly low brow circles. It does exist in Access’ DoCmd.OpenForm when you want to open the form as a dialog box.

The other major problem I have is writing comments in the VBE. Thankfully I don’t write a bunch of comments, but I do use the words With, If, and For a lot as I’m sure we all do. When I type them, they expand as if I were typing VBA statements. I tried to code something in AHK that would recognize a single apostrophe and ignore stuff that’s typed after it, but I never got it to work. It’s not simply that it didn’t work, it broke just about everything else in that AHK file. I was clearly over my head and deleted the whole attempt. I wish I had saved it so I could at least shown you what not to do.

Switching Aggregates in Pivot Fields

We’ve all been there. You create a pivot table, add your Values fields, and Excel thinks you want to Count them instead of Sum them just because you have a few blanks.

To fix it, you can click the yellow Count of Labor (for example), choose Value Field Settings, and change the aggregate. Or you can right click on any field and choose Summarize Values By and switch it to Sum. Both good options, but not good enough. I assigned Ctrl+Shft+A to this happy little customer and I’m toggling aggregates like crazy.

There’s probably a bug or two, but so far so good.

Handling Errors when Opening Outlook Attachments

Back in 2013 when I returned to using Outlook as an email client (new job, prior job used Google Apps), I was sprucing up some old code. I have two problems with the code on that page; one I’m solving here and one I don’t know how to solve yet.

The first problem is when someone sends me two attachments. I want to open the first, but have no interest in the second. Most recently this problem manifests itself as an invoice and a packing list. I need the invoice, but I don’t need the packing list. Alt+3 (this macro is third on my QAT) opens the last attachment first, so I’m stuck opening the packing list, closing it, then opening the invoice. In practice, I open it the old fashioned way (Shft+Tab, Home, Ctrl+Shft+RightArrow, Menu, O). Go ahead and try it. You know you want to. The Menu key is the key between Alt and Ctrl on the right side of my keyboard. Even if I concentrate really hard on the first attachment, the code still opens them just like a programmed. I don’t have a solution for this.

The second problem is when someone sends me an attachment with no file extension or some bullshit file extension. I get a text file with a .success extension from a website telling me my upload worked. I’m not sure if they’re just being clever or if there is some other significance, but I do know that Windows, and more specifically WScript.Shell, doesn’t know how to open it. I had some code that checked for no extension and opened it in Notepad++, but recently changed it to handle any unknown file extension.

Good ol’ error handling. If WScript.Shell can’t open the file, it throws error -2147023741, better known as

When that happens, it opens the file in Notepad++. That may not always be the best choice, but usually is. Happy keyboarding.

KeyRocket

Fellow keyboarder, Peter, said I should try KeyRocket.

KeyRocket is an application that teaches shortcuts.

Sounds right up my alley, so I download the evaluation version. It doesn’t say, or I couldn’t find, how long the evaluation lasts. I have a couple of thoughts about the install process. First, I like that when you click the Download button, you not only go to the download instructions, but the file downloads automatically instead of having to click another link. I can see how some people might not like that, but I do. Second, when the installation is complete you get this:

That’s a great message. You don’t have to do anything except read these five instructions or skip them. What I didn’t like about it? The buttons don’t have accelerators so you have to use the mouse to click Next or Skip. Deliciously ironic.

I “used” it for half a day and didn’t notice it was there. I simply don’t use my mouse, particularly in Excel, so there was nothing for KeyRocket to show me. It’s not KeyRocket’s fault; I’m just not the target customer.

The programs that KeyRocket supports are:

  • Windows – I don’t use the mouse much in Windows
  • Excel – Apparently I never use the mouse in Excel
  • Outlook – I don’t use the mouse here either. I’ve already created shortcuts for the things I do in here.
  • Powerpoint – Please. If I had to use PPT in my job, I would weep a thousand tears.
  • Word – I’ve used Word in my day job probably a dozen times in two years. That’s enough.
  • Visual Studio – I program in VBA, so no dice here.

For those programs I use often, I’ve learned the shortcuts or developed by own. The other programs that it supports, I just don’t use.

There are a couple dozen Shortcuts exclusive to KeyRocket, i.e. not built-in to Excel, but created by KeyRocket. Some of those overwrite my existing special shortcuts, so would have to re-assign those if I were sticking with it.

When I used one of the KeyRocket shortcuts, it showed a little box in the bottom right corner that said “First use of a KeyRocket Shortcut” or something like that. It was very unobtrusive. I’m really impressed with the design decisions these guys made.

After a couple days I was having problems with the VBE. I was getting Out of Memory errors and orphaned instances of Excel and the VBE wouldn’t close. I don’t have any evidence that KeyRocket was causing this, but I had to uninstall it along with a couple add-ins because I couldn’t afford to have the errors. I would have been nice to remove those one-by-one to see which was causing and it would be nice to have all the time in the world and $1 million. I don’t have any of those.

Finally, the premium version is $135 per year. It appears they have a premium version and an enterprise version, but I couldn’t tell was what the premium version was premium in relation to. Is there a standard version? Not that I could find. If premium is the base version, then $135 seems steep. Actually $135 one time would seem steep. Are they supposing that after a year you’ve learned all the shortcuts and you don’t renew? Are they supposing that big, faceless companies pay gobs for software and aren’t that price sensitive? Maybe both.

I’m unequivocally in favor of anything that teaches people keyboard shortcuts. On top of that, I was impressed by the design of this product at every turn. If you want to learn some keyboard shortcuts and your boss doesn’t mind parting with $135, give it a try. If you do try it, even for just the trial period, leave a comment with your impressions.

Tidying Up the Project Explorer

You know that pressing Ctrl+R in the VBE takes you to the Project Explorer. Of course you do. I’m not going to sit here and insult your intelligence by pretending that you don’t. But that means you are probably also just as confused as I am about which projects the VBE “chooses” to expand and which stay closed.

I have a lot of add-ins and that makes my Project Explorer very messy. Ideally I want the active project to be expanded and everything else collapsed. But I can’t have that. I decided I’ve had enough and added the following hotkey to my AutoHotKey VBE.ahk file.

^+r::
Send ^r
Send {End}
Loop 20 {
Send {Left 2}{Up}
}

When I’m in the VBE (an IfWinActive condition in VBE.ahk) and press Ctrl+Shift+R, the focus goes to the Project Explorer, goes to the last project, then closes up to 20 of them. Two left arrows will collapse an expanded project and has no effect on a collapsed project. The up arrow goes to the next project. Genius, you say? I humbly agree.

I don’t keep my modules in Folders (Toggle folders using the button at the top of the Project Explorer). If you do, you’ll need to change the {Left 2} to {Left 4}.

Until Next Year

International Keyboard Shortcut Day was a huge success. I expected that I would be the only one participating and I have pretty good evidence that a few other did. Therefore, we blew right through expectations and set the bar high in Year 1.

I put my mouse away at 2:30 and worked pretty much nonstop for an hour. At 3:30, I hadn’t touched my mouse once. I had actually opened a text file to record all the times that I was tempted to use my mouse and the keyboard method I used instead. But there weren’t any. I’m sure I go an hour without reaching for my mouse all the time, so it’s not so surprising that this hour was without temptation. I was really hoping to have some seemingly intractable problem so I could post my crazy keyboard gyrations, but alas.

I tweeted that I had completed my observance of the day and Twitter’s web client presented a big obstacle. I couldn’t compose and send a tweet without using the mouse. Normally I would ‘Find’ the text on the page and hit enter to activate the hyperlink, but it didn’t work in this case. The web is surely the worst for keyboard accessibility, but it was ironic that my IKSD tweet required using the mouse.

I think the benefit of going cold turkey for 1 hour once a year is that you’ll discover a keyboard method for accomplishing some task that you’ll continue to use beyond the day.

Please share your IKSD experience in the comments below. If you used your mouse at all, tell me what the situation was. If you discovered any shortcuts or other keyboard methods that were new to you, share those too. Not all keyboarding is shortcuts – nearly everything on the Ribbon is keyboard accessible, although sometimes it takes more keys than a guy likes.

Until next year, keep those fingers on home row.

Opening, Closing, and Saving Workbooks

Happy International Keyboard Shortcut Day. Today, three quick ones for dealing with workbooks.

: Shows the Open dailog box to open a file from disk. The menu is File – Open (Alt + F + O)

: Saves the active workbook. If the active workbook is previously unsaved, shows the Save As dialog to choose a location to save to. The menu is File – Save (Alt + F + S) for Save and File – Save As (Alt + F + A) for Save As.

: Closes the active workbook. Actually it closes the active window, but if you only have one window open, the workbook is closed. If the workbook is unsaved, Excel will prompt you to save it. The menu File – Close (Alt + F + C). Fun fact:

works to.

Bonus Material

: Closes the Excel application. Excel will prompt you for any unsaved workbooks. I still use Excel 2010, so I have no idea how the SDI works in 2013. I imagine

only closes the active workbook. If you use use Excel 2013, leave a comment.