CODEWITHSUNDEEP
vbaexcelexcel-addins
SoftTimur
SoftTimur

Reputation: 5490

Automatically add a single menu item of an add-in in Excel

I am using Microsoft Excel 2010 for Windows.

I have already developed an add-in addin.xlam, which contains a sub main. addin.xlam is at the right place so that it is visible and selectable via the menu Developer -> Add-Ins. When I open a normal workbook test.xlsm, and press Alt + F11, I can see the code of addin.xlam is loaded.

My aim is to add a single menu item to the menu bar of Excel, to allow users to launch main of add-in.xlam. By following this link, my code in addin.xlam is as follows:

Option Explicit
Dim cControl As CommandBarButtonPrivate
Sub Workbook_AddinInstall()
    On Error Resume Next 'Just in case

    'Delete any existing menu item that may have been left.
    Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete

    'Add the new menu item and Set a CommandBarButton Variable to it
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add

    'Work with the Variable
    With cControl
        .Caption = "Super Code"
        .Style = msoButtonCaption
        .OnAction = "main" 'Macro stored in a Standard Module
    End With
    On Error GoTo 0
End Sub

Private Sub Workbook_AddinUninstall()
    On Error Resume Next 'In case it has already gone.
    Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
    On Error GoTo 0
End Sub

This code is well placed in ThisWorkbook of addin.xlam, it is also visible in test.xlsm. But I can't see any change in the menu bar.

Does anyone know what happens?

Upvotes: 2

Views: 11759

Answers (2)

peng W.
peng W.

Reputation: 1

Charles is right, you need to replace Workbook_AddinInstall() with Workbook_Open(), and replace Workbook_AddinUninstall() with Workbook_BeforeClose().

furthermore, you need CommandBarButton not CommandBarButtonPrivate.

good luck!

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23505

The AddinInstall and AddinUninstall events are only fired when the the addin is "installed" or "uninstalled" using the Excel Addin Manager.

IMHO this can lead to problems, so I always recommend using the Workbook_Open and Workbook_BeforeClose events instead.

Upvotes: 5

Related Questions