Excel 2010 Add-in WorkBookOpen Issue

[pgsmick]

Hi,

In developing a 2010 Add-In I have run into what looks like a chicken/egg problem.

I want the addin to check every workbook that Excel opens (whether the user opens Excel first and then uses File Open, or opens the file directly from the desktop or other location) and do something special to the workbook if it meets certain criteria. So I have created code like so in the ThisWorkBook module of the Add-In:

Public WithEvents app As Application
-------------------------------------------------
Public Sub WorkBook_Open()


Set app = Application


End Sub
---------------------------------------------------
Public Sub App_WorkbookOpen(ByVal Wb As Workbook)


Call Check4Report


--------------------------------------------------
Public Sub Check4Report()
Dim NamePart 

'Application.Wait (Now() + TimeValue("0:00:05"))

[B]NamePart = Left(ActiveWorkbook.ActiveSheet.Name, 6)[/B]
If NamePart = "report" And HasText("Confidential Information - Do Not Distribute") Then
    On Error Resume Next
    SheetProtect ("Protect")
    BookProtect ("Protect")
    MsgBox "The existing data cells in this report are now locked.  You may add new data using the macros provided.", vbOKOnly
    
End If


End Sub

When I open a workbook, I get an immediate end/debug runtime error 91 on the highlighted line complaining about object variable not set. The workbook does not appear to be opened yet. If I hit debug and wait a few seconds, I will see the workbook open and then when I press F5 in VBE, the code will complete as desired.

I have tried inserting a delay in the Check4Report using Application.Wait command, but that did not help

How do I get my code to wait for the workbook to fully open before running the problematic code?

Thanks,
Peter

 

[ParamRay]

You should use the Wb variable within the WorkbookOpen event handler, and pass it by reference to your Check4Report procedure as an argument, rather than using ActiveWorkbook.

 

[pgsmick]

Thanks.