Opening the Addin Dialog like a Pro

Back in the old days when Excel had menus and toolbars, a guy could use Alt+t+i to open the Addins dialog (Tools – Addins). But that would only work if there was an open workbook. No open workbook, no dialog. Now in the days of the Ribbon, the shortcut is Alt+f+t a a Alt+g (File – Options – Addins – Go). You don’t need to have a workbook open, which is nice, but there is a bit of delay between the two “a’s” in the keyboard sequence.

MS did a wonderful thing when they made the old 2003 menu navigation still work in later versions. Even though there’s no longer a Tools menu, you can still use Alt+t+i to open the dialog. Unfortunately you still need to have a workbook open for it to work. I can’t imagine why that is, but it is.

Well, it’s VBA to the rescue. You can show most any dialog with Applicaiton.Dialogs().Show. But showing the Addins dialog returns an error if there is not an active workbook, just like with the old menus. It’s trivial enough to fix, to wit:

Sub ShowAddinDialog()


    Dim wb As Workbook
    'Dialog won’t show if there’s no workbook showing

    If ActiveWorkbook Is Nothing Then

        Set wb = Workbooks.Add

    End If
    'Show addin dialog

    Application.Dialogs(xlDialogAddinManager).Show
    'Close wb if it was created

    On Error Resume Next

        wb.Close False

End Sub

That creates a new workbook if needed, then shows the dialog. It keeps track of whether it created a workbook and, if so, closes it without saving. Hardly worth your time to read this post, you say? You already knew about this, you say? Here’s the real magic. Those old 2003 commandbars still lurk behind the scenes in Excel. If you create new ones, they show up on the Add-ins tab. But you can modify the existing one too. I put this little gem in the Auto_Open macro in the same workbook as my ShowAddinDialog procedure.

With Application.CommandBars(1).Controls("Tools").Controls.Add(msoControlButton, , , 1) .Caption = "&I" .OnAction = "ShowAddinDialog" End With

And then to clean it up in Auto_Close.

On Error Resume Next Application.CommandBars(1).Controls("Tools").Controls("I").Delete

Commandbars(1) is the menu and Controls(“Tools”) is the Tools menu. I add a new control to position 1 on that Tools menu. I don’t need a fancy caption because I can’t see it anyway. I just need a caption with I as the hotkey. Whichever letter follows the ampersand (&) is the hotkey. The built-in addins menu item has a caption of Add-&Ins... making I the hotkey for it. But mine is higher up, so it wins.

In the previous post I referenced above, I add this macro to the QAT. But the muscle memory of Alt+t+i dies hard. Rather than retrain myself like a normal person, I’m embracing my quirks. I can now use Alt+t+i and get the desired results.

6 thoughts on “Opening the Addin Dialog like a Pro”

This might be sufficient:

Sub M_snb() GetObject("", "Excel.application").Workbooks.Add.Application.Dialogs(321).Show End Sub

Gee, I don’t find it too much trouble to do Ctrl+N, Alt+T+I to open the Add-Ins dialog, then Ctrl+W when I’m done.

Thanks for the tip! I never knew about Alt+T+I.

You can also add the Add-ins button to the QAT. It’s on the Developer tab and you can right click > Add to QAT (easier than searching through the commands menu). The COM Add-ins button is also there and I keep it in the QAT because my PowerPivot add-in tends to disappear a lot in 2010. It’s a quick way to open it back up.

Me too Jon, I have the addins button on the QAT, for the same reason. The registry key HKCU\software\Microsoft\Office\Excel\Addins for PowerPivot shoud be 3, which it was on my machine, and I was pointed at http://support.microsoft.com/kb/2805999/EN-US, even though PowerPivot isn’t missing from the list. I have tried this and it hasn’t disappeared since.

@DK,
Why do need to work with the Addins dilaog and not have a File open – Then Alt+t+i works
@SNB
Nice approach, but You code creates problems if you have side by side installs various Excel versions
Also I faced this problem long ago – in some cases the Application.Dialogs(X).Show – The “X” varies across versions for the same dialog don’t remember which dilaog it was