Excel Add-In Context Menu Buttons

Question

I have an Excel Add-In that I have created ( .xlam file ) I am trying to have this add-in create buttons on the right click menu so that I can quickly run a macro based on the selected cell. I added code to ThisWorkbook, and it works in the xlsm file. (I copied the code from HERE.) I SaveAs to an xlam, load the add-in, and I get nothing on the context menu. I have a feeling these subs do not load through the add-in. However, I can get code in Workbook_Open to function. Can someone point me in the right direction?

Private Sub Workbook_Deactivate()
    On Error Resume Next
    With Application
        .CommandBars("Cell").Controls("Open Drawing").Delete
    End With

    On Error GoTo 0
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim cmdBtn As CommandBarButton
    On Error Resume Next

    With Application
        .CommandBars("Cell").Controls("Open Drawing").Delete
        Set cmdBtn = .CommandBars("Cell").Controls.Add(Temporary:=True)
    End With

    With cmdBtn
       .Caption = "Open Drawing"
       .Style = msoButtonCaption
       .OnAction = "Open_Drawing_Main"
    End With

    On Error GoTo 0
End Sub

Ibo · Accepted Answer · 2018-08-09 20:34:20Z

4

You have to use AddinInstall event. I assume that your context menu procedure works fine. So double click on ThisWorkbook in the add-in file (before you install it and save it as xlam), select AddinInstall event from the upper write dropdown menu and place some code like this:

Private Sub Workbook_AddinInstall()
    Call AddToRightClickMenuOptions_Main
End Sub

in this code AddToRightClickMenuOptions_Main is the sub that creates the context menu. Note that you may want to remove the context menu when you uninstall the add-in:

Private Sub Workbook_AddinUninstall()
    Call DeleteFromRightClickMenuOptions_Main
End Sub

where DeleteFromRightClickMenuOptions_Main is the routine that removes the context menu (you can find the code easily on the internet)

edited Aug 9, 2018 at 20:34

answered Aug 9, 2018 at 20:05

Ibo

4,3197 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mistella Over a year ago

Q: Will Workbook_AddinInstall() run in the non-addin workbook when an addin is installed, or in the addin itself? I always assumed this would trigger in the workbook for which an add-in was being installed.

Ibo Over a year ago

A: yes, the context menus work with the right click on a sheet. Once an add-in is installed the user does not see its worksheet and the active worksheets are the ones that provide the functionality

Mistella · Accepted Answer · 2018-08-09 20:14:32Z

An Excel Add-in (at least, the ones I have created) still have their own workbook. The Workbook_SheetBeforeRightClick and Workbook_Deactivate will run when the .xlam's workbook is either right-clicked or deactivated. If you want the code to run only for when the selected workbook is right-clicked or deactivated, you'll have to have the code in that specific workbook's code.

However

Since you are trying to get this to work with an add-in, I'm going to go through a few relevant points since once an Add-in is loaded, it (and it's features) are accessible from all other open workbooks.

If the option is added to the context menu whenever a page is right-clicked in any book (which it would (as coded, if it worked)), removing the context menu option when any workbook is deactivated won't have any visible effect, as the next time a right-click happens the option will once again be visible.
Is there a reason why the context menu can't just be added when the add-in is opened (add-in's Workbook_Open), and removed when it's closed(add-in's Workbook_BeforeClose)? (As this will visibly have the same effect as the above code.)

Collectives™ on Stack Overflow

Excel Add-In Context Menu Buttons

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related