Workbook event will not fire in Add-in

[Sorwen]

I'm trying to run the following code from an add-in, but for some reason the event never fires off. If I move it to the current workbook itself it is fine. So I assume it is some issue with Add-ins or is there another event I could use for an Add-in?

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As 

Object, ByVal Target As Range, Cancel As Boolean)
Dim CheckForText As Object
Dim CurrentShortcutMenu As CommandBar
Dim CurrentMenuItem1 As CommandBarControl, CurrentMenuItem2 As CommandBarControl

Set CurrentShortcutMenu = Application.CommandBars("Cell")
Set CheckForText = Selection.Comment
Set CurrentMenuItem1 = CurrentShortcutMenu.Controls.Item("Copy Comment")
Set CurrentMenuItem2 = CurrentShortcutMenu.Controls.Item("Paste Comment")

If Not CheckForText Is Nothing Then
    CurrentMenuItem1.Visible = True
    CurrentMenuItem2.Visible = True
ElseIf CheckForText Is Nothing Then
    If CurrentMenuItem1.Visible <> False Then
        CurrentMenuItem1.Visible = False
        CurrentMenuItem2.Visible = False
    End If
End If

End Sub

 

[rmikesmith]

As you are probably aware, worksheets are never visible/active in an Add-In (unless you set the AddIn property to false during development). Therefore, event procedures related to the Add-In's worksheet actions will not fire. Add-Ins typically act on other workbooks that are open. However, in order to work with events triggered by these "external" workbooks, your Add-In must establish Application-level events. How to do this? Chip Pearson, Microsoft Excel MVP, has a short tutorial and example workbook on his website. Check out:

http://www.cpearson.com/excel/AppEvent.htm

Regards,
Mike

 

[Sorwen]

Thanks!

 

[Sorwen]

While helpful it doesn't work either. You have to set part of it in the open work book event which doesn't fire off either. It just doesn't seem possible for me to make what I needed. Thanks for the help though.

 

[rmikesmith]

Sorwen,

Don't give up that easily! I'm quite sure it will work. I have used just such a technique to monitor events in open workbooks from within an Add-In. Explain what you mean by

You have to set part of it in the open work book event which doesn't fire off either.

Are you referring to instantiating the class variable in the Workbook_Open procedure?


Regards,
Mike

 

[Sorwen]

To quote the page.

This declares a new instance of the EventClass named AppClass. This provides a link between your workbook code and the application EventClass. Next, you've got to set AppClass to the current Excel Application. Enter the following code in the Workbook_Open event procedure for your workbook:

Set AppClass = New EventClass
Set AppClass.App = Application

If desired, you can move the code to initialize the App object to the Initialize event of the class itself. This event is automatically executed when a new object based on the class module is created. There is also a corresponding Terminate event.

Private Sub Class_Initialize()
Set App = Application
End Sub

So at the least Set AppClass = New EventClass has to be in the Workbook_Open event to start it all off.

 

[rmikesmith]

OK,

Here is how I did this in an Add-In:

In my class module I have the following declaration

Public WithEvents App As Application

In the ThisWorkbook module of my Add-In I have the following:

Dim AppClass As New EventClass

Private Sub Workbook_Open()
   Set AppClass.App = Application
   
   ... Other initialization code

End Sub

Back in the class module, the SheetBeforeRightClick procedure would look like this:

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  'Your code here
End Sub

Note the difference in the procedure name from that of a workbook event.

Now, if your Add-In's Workbook_Open event isn't firing, something else is going on; not normal behavior.


Mike

 

[Sorwen]

Yes, for some reason in my Add-In the Workbook_Open event never goes off. I even put just a MsgBox to test and it never pops up. I may put it in and try it on another computer to see what happens.

 

[rmikesmith]

A couple of things to check...

Has the Application.EventsEnabled property been set to False anywhere in your code? Inspect it using the Immediate Window in the VBE.

Is the Workbook_Open procedure in the ThisWorkbook module of your Add-In?

Mike

 

[Sorwen]

I retyped the whole thing and now it works. I have no clue what was wrong before. Thanks for the help.