How to assign a shortcut key to the Explode Excel addin

Sorry to bore you with yet another Explode addin post, but I think this is worth it on a couple of fronts. Assingning a shortcut key to the explode plug-in makes more awesome - being able to devle into those dependants even quicker.

Also seeing as as my initial explode post is one of my most read posts, I know you'll all lap it up.

There are two ways of doing this, either to assign a shortcut in the ribbon macro window this is by far the easiest method, or to code it in VBA.

First Method

The trick here is that you need to know the name of the Sub which calls the main prog, however since the VBA project is locked for viewing you'll have to trust me in knowing what it is called.....

First of all you have to enable the developer ribbon - instructions here.

Then its just a matter of clicking the Developer tab, selecting the Macros button and type the following in the top box:

Explode.xla!ShowExplodeForm

If you spelled it correctly the options button should make itself available where you can then input your shortcut of choice. You'll need to try to avoid an already take key combo (so stick to Ctrl-Shift-something), my preference is Ctrl-Shift-X. To enter this just hold down shift and type X in the box then hit OK. You can then cancel the dialogue and merrily save a few seconds a day!

Second method

The next method is to insert some code into the ThisWorkbook object in the addin's VBA project, however since we've already established it is locked - I have a workaround - we'll put it in our Personal Macro workbook.

First off we'll have to enable the addins references to call from our code, from the menu bar go to Tools then References and check the VBA_Explode box.

Then all we need to do is paste the following code in your personal macro book's ThisWorkbook object:

Option Explicit

Private Sub Workbook_Open()
       
    Application.OnKey "^+X", "VBA_Explode.ShowExplodeForm"
End Sub

Hit save up at the top and reopen Excel and test it out - Ctrl-Shift-X should get you rockin'.