CODEWITHSUNDEEP
excelvbaadd-inright-click
Kzr
Kzr

Reputation: 21

Right-click withevents works on source .xlsm but not on .xlam addin

I've made a couple of macros that run through right click menu button based on the cell value. Typically, if I right click on cell with value 'XYZ', the menu button shows as 'Run macro for XYZ' and then does a bunch of operations: show a couple of user forms, run an SQL query, show and format result data.

On the original .xlsm file, on 'Thisworkbook' I have the following code:

Public WithEvents mxlApp  As Application

Public WithEvents mxlSh  As Worksheet

Private Sub mxlApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As 
Boolean)

... (do stuff here) ...

End Sub

...

Private Sub Workbook_Open()

    Call AutoExec

End Sub

...

On a separate module, I have the following function used to set my event handler

Public Sub AutoExec()

        Set mxlApp = Application

        Set ColectionOfMxlEventHandlers = New Collection

        ColectionOfMxlEventHandlers.Add mxlApp

        Debug.Print ThisWorkbook.Name & " Initialized"

End Sub

The problem: on the original .xlsm file, the code works fine: every time I right-click on a cell which meets certain criteria, I get the 'Run macro for XYZ' and all is fine.

Once I save the file as .xlam and load it as addin, the code won't work.

I have been looking everywhere on the internet and here and couldn't figure out how to resolve this issue.

EDIT:

After modifying the code as kindly suggested by creamyegg, this is what I have:

In class module clsAppEvents:

Private WithEvents mxlApp As Excel.Application

Private Sub Class_Initialize()

    Set mxlApp = Excel.Application

End Sub

Private Sub mxlApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cBut As CommandBarButton

    On Error Resume Next

        Call CleanMenu

        If Len(Target.Value) = 8 Then

            MyId = Target.Value

            With Application

                Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)

            End With

            With cBut

               .Caption = "Run SQL Query for " & MyId

               .Style = msoButtonCaption

               .FaceId = 2554

               .OnAction = "CallGenericQuery"

            End With

        End If

        With Application

                Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)

        End With

        With cBut

           .Caption = "Columns_Select"

           .Style = msoButtonCaption

           .FaceId = 255

           .OnAction = "CallShowHide"

        End With

    On Error GoTo 0

End Sub

in Thisworkbook class I have

Public m_objMe As clsAppEvents

Private Sub Workbook_Open()

    Set m_objMe = New clsAppEvents

    Debug.Print ThisWorkbook.Name & " Initialized"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next

           Call CleanMenu

    On Error GoTo 0

    Set m_objMe = Nothing

End Sub

Private Sub Workbook_Deactivate()

    Call CleanMenu

End Sub

MyId is defined as a public string in the main module containing the CallShowHide and callGenericQuery subs

Upvotes: 2

Views: 934

Answers (1)

markblandford
markblandford

Reputation: 3193

The issue sounds like your WithEvents is still in your ThisWorkbook Class? What you need to do is create a new class and then instantiate an instance of this on the Workbook_Open() event of your add-in. For example:

New Class (clsAppEvents):

Private WithEvents mxlApp As Excel.Application

Private Sub Class_Initialize()
    Set mxlApp = Excel.Application
End Sub

Private Sub mxlApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
...
End Sub

Add-in ThisWorkbook Class:

Private m_objMe As clsAppEvents

Private Sub Workbook_Open()
    Set m_objMe = New clsAppEvents
End Sub

Private Sub WorkbookBeforeClose(Cancel As Boolean)
    Set m_objMe = Nothing
End Sub

Upvotes: 1

Related Questions