Excel 2010 Add-in WorkBookOpen Issue

pgsmick

New Member
Joined
Apr 29, 2013
Messages
8
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:

Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,615
Messages
5,625,872
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top