Andy Holaday
New Member
Being the steadfast keyboard user that I am, I have been silently lamenting for years that Excel 2003 does not offer a robust way to create keyboard shortcuts. True, you can assign a macro to fire with Ctrl + {key of your choice} but most of the choices are already associated with useful Excel or Windows actions.
I have been building up a collection of time-saving macros over the years and creating toolbar buttons for them. But of course, the only way to actuate a macro via a toolbar button is to release one hand from the keyboard and use the mouse -- something I avoid whenever possible.
Today I stumbled on a usenet message that mentioned the Application.OnKey method. I am sure this method is not news to many of you, and I certainly have seen the name tossed around here and there, but today the lightbulb finally came on in my brain: How can I make this method work for me?
I keep all my spiffy macros in an add-in. Today, I added this code to the ThisWorkbook module of my add-in:
Private Sub Workbook_Open()
Application.OnKey "^+a", "AccountingFormat"
Application.OnKey "^+p", "One_Click_Pivot_Table"
Application.OnKey "^+t", "TidyText"
End Sub
And now three of my favorite macros are now only one keystroke away.
Why keyboard shortcuts? What's wrong with toolbars?
For me there are two problems with toolbar buttons.
The first is the latency with using the mouse. Compared to using a keystroke it takes much longer to swirl a mouse around and hunt down a button to click. True, we are talking about a couple seconds per click, and at the end of the day I might spend, say, three minutes using the mouse for tasks I would rather spend, say, 10 seconds doing with the keyboard. Three minutes is not a huge time savings, but when I am working I am usually thinking a few steps ahead and want to get to the hard part of the anlaysis quickly. The mouse just slows me down.
The second has something to do with memory. Human memory, that is. Toolbar buttons have a way of being ambiguous. Petite icons do not register their intent well on my brain. I do associate those little pictures with actions, but not because the pictures are representative of the actions. Rather, I have hard-coded the picture-action relationships in my memory. Unfortunately, unless the action is one I call upon frequently, I spend a lot of time mousing around to remind myself what the little pictures do. A keyboard action, on the other hand, can be instantaneous. Muscle memory is much quicker than the hand-on-mouse/find-picture/decode chain of events, even if I know what picture I am looking for.
I hope Excel keyboarders out there find this useful.
I have been building up a collection of time-saving macros over the years and creating toolbar buttons for them. But of course, the only way to actuate a macro via a toolbar button is to release one hand from the keyboard and use the mouse -- something I avoid whenever possible.
Today I stumbled on a usenet message that mentioned the Application.OnKey method. I am sure this method is not news to many of you, and I certainly have seen the name tossed around here and there, but today the lightbulb finally came on in my brain: How can I make this method work for me?
I keep all my spiffy macros in an add-in. Today, I added this code to the ThisWorkbook module of my add-in:
Private Sub Workbook_Open()
Application.OnKey "^+a", "AccountingFormat"
Application.OnKey "^+p", "One_Click_Pivot_Table"
Application.OnKey "^+t", "TidyText"
End Sub
And now three of my favorite macros are now only one keystroke away.
Why keyboard shortcuts? What's wrong with toolbars?
For me there are two problems with toolbar buttons.
The first is the latency with using the mouse. Compared to using a keystroke it takes much longer to swirl a mouse around and hunt down a button to click. True, we are talking about a couple seconds per click, and at the end of the day I might spend, say, three minutes using the mouse for tasks I would rather spend, say, 10 seconds doing with the keyboard. Three minutes is not a huge time savings, but when I am working I am usually thinking a few steps ahead and want to get to the hard part of the anlaysis quickly. The mouse just slows me down.
The second has something to do with memory. Human memory, that is. Toolbar buttons have a way of being ambiguous. Petite icons do not register their intent well on my brain. I do associate those little pictures with actions, but not because the pictures are representative of the actions. Rather, I have hard-coded the picture-action relationships in my memory. Unfortunately, unless the action is one I call upon frequently, I spend a lot of time mousing around to remind myself what the little pictures do. A keyboard action, on the other hand, can be instantaneous. Muscle memory is much quicker than the hand-on-mouse/find-picture/decode chain of events, even if I know what picture I am looking for.
I hope Excel keyboarders out there find this useful.
