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:

Private Sub Workbook_Open()
Application.OnKey “%{F12}”, “OpenPowerPivot”
End Sub

  • Now open the Modules folder and double click Module 1
  • Replace the code in there with this:

Sub OpenPowerPivot()
On Error Resume Next

Select Case Val(Application.Version)
Case Is = 14
Application.SendKeys “%GY2″
Case Is > 14
Application.SendKeys “%BM”
End Select

On Error GoTo 0
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.

4 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?

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>