A COM Add In must handle WorkbookOpen event

[Maurizio BELLANTONE]

In an already working well VB6 written COM addin, in order to obtain that my
COM Add-in intercept WorkbookOpen event I added these lines to the
Connect.dsr code:

***
Dim oMyApp As New ClassXlApp
....
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal
ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom()
As Variant)
On Error Resume Next

Set oXl = Application
Set oMyApp.App = Application
....
***

Instead ClassXlApp contains:

***
Public WithEvents App As Excel.Application
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim sExt As String

On Error GoTo Linea12
MsgBox "ClassXlApp - You open a sheet!!!"
Linea12:
On Error GoTo 0
End Sub
***

but no message box appear.
What I forget to add to my code ?
TIA,
Maurizio

=========================================================
BELLANTONE MAURIZIO - Teacher, IT Consultant, Programmer
SPINETTA MARENGO (AL) - ITALY

 

[Peter T]

Hi Maurizio,

Did you set a reference to Excel (lowest version of any user) as well as a
listed application in the Connect designer class.

If the com is designed to work with multiple applications it would be an
idea to do something like this

If LCase(Application.Name) = "microsoft excel" Then
Set oMyApp.App = Application
Msgbox "XL events running", ,oMyApp.App.Name ' for testing only
Else
Msgbox "XL events NOT running" 'for testing only

End If

do you also need this
Set oXl = Application

Regards,
Peter T

 

[Maurizio BELLANTONE]

Thank you. I resolved.
Removing "On Error" statements did not modify add-in execution because no
error event occurs: cause of the problem was incomplete uninstallation of
add-in previous versions from the VM used to test it.
After a manual clean of some registry keys, after a new setup add-in works
better.

BUT there is another problem: only users with administrators privileges can
use it.
A MS support document (
http://support.microsoft.com/default.aspx?scid=kb;EN-US;290868 ) state that
setting registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins\MyAddin.Connect

value to 3 must resolve, but it didn't.
Regards,

Maurizio

=========================================================
BELLANTONE MAURIZIO - Teacher, IT Consultant, Programmer
SPINETTA MARENGO (AL) - ITALY