ActiveWorkbook is nothing in Workbook_Open

[alexagran]

I've built an Excel add-in that needs to execute some code when the
workbook opens. I've added the following code to the Workbook_Open
event:

Private Sub Workbook_Open()

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = crFinancialsSheet Then
sheetExists = True
End If
Next

End Sub

I add the add-in, close the workbook and open a new instance of Excel.
At this point, the add-in throws an error because the ActiveWorkbook in
the Workbook_Open event is nothing and obviously there are no sheets (I
can visually see that the sheets are not there).

What I'm wondering is why the ActiveWorkbook is nothing in the
Workbook_Open event and is there another event that I can put my code.
I need the workbook to have sheets.

Thanks,

alex

 

[Tom Ogilvy]

the workbook_open event in the addin fires when the addin is loaded. I
suspect you want to have the code fire when another workbook is opened in
Excel. If that is the case, then you need to use application level events
which can be instantiated from your addin

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

for some guidance.

 

[Die_Another_Day]

Are you wanting this triggered when any workbook is opened? If so this
will require a Class Module to handle an application level event. What
is causing the error is the fact that as Excel opens, it opens the
Addins, Personal.xls, and other items in the "xlStart" directory. Your
addin runs in the background and therefore cannot be the
"ActiveWorkbook". After all the Addins are loaded then Excel creates
the new workbook, "Book1". Anyhow here is some code to get you going on
the Application Level event:
Place this code in a class module:
Option Explicit

Public WithEvents App As Application

Private Sub App_Workbook_Open()
'Your Code
End Sub

And this in a normal module
Option Explicit
Public AppClass As New EventClass

And this in the "ThisWorkbook" Object:
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Let me know if you need help or if I'm totally off on what you're
trying to do.

Charles Chickering

 

[alexagran]

Thanks, Tom...

alex

the workbook_open event in the addin fires when the addin is loaded. I
suspect you want to have the code fire when another workbook is opened in
Excel. If that is the case, then you need to use application level events
which can be instantiated from your addin

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

for some guidance.