Check your references in VBE.

if ANY reference is listed as ISMISSING this may well cause the hiccup.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ThankYou_jeff wrote :

Excel add-in has been working for 2 years using ado 2.7; it fails on
ado 2.8. Here's stripped down version that displays message boxes
for all events used. This test works on ado 2.7. It has a reference
to "Microsoft ADO Ext.2.7. for DDL and Security". I load this Add-in
programmatically. All events fire as expected.

Now when i use this same Add-in on a system with ADO 2.8 {in my test
case XP SP2} it begins ok. The FIRST, SECOND and THIRD EVENT occur
correctly then the events no longer fire. For example the right-click
does not occur, etc. If i change the ado reference to "Microsoft ADO
Ext.2.8. for DDL and Security". It works fine... events continue to
fire.

I can cheat and have two Add-ins; one with ado 2.7 and one with 2.8.
There has to be a better way. Can you input?



----------------------------------------------------------------------
----------

'***EXCEL WORKBOOK OF ADD-IN***
Option Explicit
Dim AppClass As New AppEventClass

Public Sub Workbook_Open()
MsgBox ("Public Sub Workbook_Open() FIRST EVENT")
Set AppClass.App = Application
End Sub

----------------------------------------------------------------------
----------

'***AppEventClass - an Excel VBA class module***
Option Explicit
Public WithEvents App As Application

Private Sub app_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
MsgBox ("Private Sub app_SheetBeforeRightClick RIGHT CLICK")
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox ("Private Sub App_SheetActivate(ByVal Sh As Object)")
End Sub

Private Sub App_WorkbookAddinUninstall(ByVal Wb As Workbook)
On Error Resume Next
MsgBox ("Private Sub App_WorkbookAddinUninstall(ByVal Wb As
Workbook)") Call removeMe(Wb.Name)
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
MsgBox ("Private Sub App_WorkbookBeforeSave")
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
MsgBox ("Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
NAME = " & Wb.Name)
If Wb.Name = Workbooks.Item(1).Name Then Call removeMe(Wb.Name)
End Sub

Private Sub removeMe(removing As String)
MsgBox ("Private Sub removeMe() " & removing)
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox ("Private Sub App_WorkbookOpen(ByVal Wb As Workbook) " &
Wb.Name & " THIRD EVENT")
End Sub

Private Sub Class_Initialize()
MsgBox ("Private Sub Class_Initialize() SECOND EVENT")
End Sub