sheetbeforerightclick and addin

[ukdane]

I have some code in the sheetbeforerightclick on ThisWorkbook.
You can see the code below.
[VBA]
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim MyMenu As CommandBarButton

On Error Resume Next
With Application
.CommandBars("Cell").Reset
Set MyMenu = .CommandBars("Cell").Controls.Add(Temporary:=True)

End With
With MyMenu
.Caption = "Filter"
.Style = msoButtonCaption
.OnAction = "Filtershow"
End With



On Error GoTo 0
Set MyMenu = Nothing
End Sub
[/VBA]

However I've tried to make the .xls into a .xla
As soon as I do this, because the code is found on the ThisWorkbook the right click no longer works. As it is not found on ThisWorkbook for the new book.

How do I get this code to work for the addin?

Thanks i.a.

[Bob Phillips]

[vba]

Option Explicit

Private WithEvents App As Application

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim MyMenu As CommandBarButton

On Error Resume Next

With Application

.CommandBars("Cell").Reset
Set MyMenu = .CommandBars("Cell").Controls.Add(Temporary:=True)
End With

With MyMenu

.Caption = "Filter"
.Style = msoButtonCaption
.OnAction = "Filtershow"
End With

On Error GoTo 0

Set MyMenu = Nothing

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set App = Nothing
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code