Search code examples
excelvbacomexcel-addins

VBA macro to open "Options" when opened

I would like to have a Microsoft Excel workbook display the "Manage: COM Add-ins" dialog box when a workbook is opened by the user. I am testing this on Excel 2016, but also have 2019 at my disposal. (My production environment uses one or the other.)

I tried this code, which opens the "Excel Add-ins" instead of the "COM Add-ins", and then returns an invalid procedure call:

Sub OpenComAddInsOptions()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Add-Ins...").Execute
    Dim addInWindow As Object
    Set addInWindow = Application.CommandBars("Add-Ins").Controls("COM Add-Ins")
    addInWindow.Execute
End Sub

I tried a variety of "Controls" variations in this code, but could not find any that worked.

I next thought that I could send the keycodes to open the "Options" window and then navigaqting to the "Add-ins" section and finally "COM Add-ins" using this code, but it does not open the "Options" window but instead returns a "400" error:

Sub OpenManageComAddInsOptions()
    'Open Excel Options window and navigate to Add-ins section
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.SendKeys "%FT"
    Application.SendKeys "{DOWN 8}"
    Application.SendKeys "%A"
    Application.SendKeys "{DOWN}"
    Application.SendKeys "%G"
End Sub

I did test just the SendKeys "%ft" and it does not open the "Options" window. To test syntax, I tested SendKeys "%pm" and this worked to open the Page/Margins window, then confirmed that Alt+f+t opens "Options" from the keyboard.

I then tried this code to just open the "Options" menu, but they both also return a "400" error:

Sub OpenExcelOptions()
    Application.Dialogs(xlDialogOptions).Show
End Sub

and

Sub OpenExcelOptions()
Application.ExecuteExcel4Macro "SHOW.DIALOG(400,,1)"
End Sub

I confirmed that the Dialog "xlDialogPrint" and "xlDialogOpen" open their respective windows, so this syntax should be correct also. Microsoft gave me the "xlDialogOptions" language, to no avail.

Does anyone have any other suggestions?

Solution

  • I would like to have a Microsoft Excel workbook display the "Manage: COM Add-ins" dialog box when a workbook is opened by the user.

    Try the CommandBars.ExecuteMso method

    Application.CommandBars.ExecuteMso "ComAddInsDialog"