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 Comments

  1. snb says:

    This might be sufficient:

    Sub M_snb()
        GetObject("", "Excel.application").Workbooks.Add.Application.Dialogs(321).Show
    End Sub
  2. Jon Peltier says:

    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.

  3. Jon Acampora says:

    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.

  4. Bob Phillips says:

    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.

  5. sam says:

    @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

  6. Ron de Bruin says:

    Alt-TI will always do the job for me

    Alt-TMS I also use for displaying the security dialog

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: