Giving PowerPivot a Keyboard Shortcut

When I was down at Microsoft for the MVP Summit, I suggested that, as the “other most powerful part of Excel” that PowerPivot, like the Visual Basic Editor, should be given it’s own keyboard shortcut.  Since Alt + F12 is currently empty, and since it’s right next to the VBE’s Alt + F11 shortcut, it only seems logical that Alt + F12 would make a logical choice.

Well, I got tired of waiting… not that I ever expected they’d release a patch for that or anything.  So I up a macro to do it.  It’s pretty short, works with Excel 2010 and 2013, and can be stored in the Personal Macro Workbook.*

If you know your macros, or you already have code in your personal workbook, then add the Workbook_Open line and the Sub OpenPowerPivot to your project.  But if you’ve never written a macro, or you’ve never used the personal macro workbook, here’s how you do it:

  • Expose the Developer tab
  • Click on “Record New Macro”
  • Choose to store it in the Personal Macro Workbook.  (That will create your personal macro workbook.)
  • Stop recording
  • Press Alt + F11
  • Find VBAProject(Personal.xlsb) in the Project Explorer.  (The project explorer is the treeview on the left.  If it’s not showing, press CTRL+R to display it.)
  • Double click the ThisWorkbook module inside it
  • Paste the following code in the code pane:
  1. Private Sub Workbook_Open()
  2.      Application.OnKey "%{F12}", "OpenPowerPivot"
  3. End Sub
  • Now open the Modules folder and double click Module 1
  • Replace the code in there with this:
  1. Sub OpenPowerPivot()
  2. On Error Resume Next
  3.  
  4. Select Case Val(Application.Version)
  5.     Case Is = 14
  6.          Application.SendKeys "%GY2"
  7.     Case Is > 14
  8.          Application.SendKeys "%BM"
  9. End Select
  10.  
  11. On Error GoTo 0
  12. End Sub

  • Close the Visual Basic Editor
  • Close Excel and say Yes when prompted to save changes to the Personal Macro Workbook
  • Re-open Excel and Press Alt+F12

Isn’t that just the height of lazy?  (I mean efficient!)  Smile

*Just a quick note here… if you float back and forth between Excel 2010 and Excel 2013 on the same machine, Excel locks the personal macro workbook for editing when you open the first Excel instance.  So if you open a new copy of Excel, you’ll get a prompt.  Just say okay though, and it will still work fine.

7 thoughts on “Giving PowerPivot a Keyboard Shortcut

  1. Great job! SendKeys is something I don’t use all that much but I can see new uses for it.

    The only problem I had was for it to work, I needed to replace “%GY2? with “%GY6?.

  2. Seriously? Doh! I’ll have to check, but maybe the GY# is dependant on other add-ins and such. Fortunately 2013 has an official set of letters now.

  3. It should be doing exactly that… what is it opening for you?

    Try going to the PowerPivot tab and pressing ALT, then G. What is the shortcut for the Manage window?

  4. With Excel 2010, Ver 14.0.7xxxx I could not get the above VBA code to work until I replaced
    Application.SendKeys “%GY2?
    with
    Application.SendKeys “%ftaa%a{down}~g{down} ~”
    Note the SPACE before the last tilde (~).

  5. Clarification:
    The row
    Application.SendKeys “%GY2″
    must be Preceded (not Replaced) by the new row
    Application.SendKeys “%ftaa%a{down}~g{down} ~”
    Note the SPACE before the last tilde (~).

    Also, all Open and Close Quotes in the VBA code above must be replaced by the simple Quote (key combination Shift-‘) or else you get syntax errors in VBA.

  6. Hi Dan,

    I updated the post to use code tags. That should force the ” characters to display as the proper quotes, not the curly ones that wordpress seems to think are pretty. (Grrr.)

    With regards to the SendKeys method, it’s starting to become clear that the tab shortcut can’t be relied on to be consistent between installs. :(

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>