Run-time error 9 when running add-in

[RobH]

I have successfully set up some code in my PERSONAL.XLS sheet, which
automatically runs a macro whenever a spreadsheet is opened. For the
purposes of rolling this out, we want to set it up as an add-in. When
I do this, the same code falls over with a run-time error 9.

When I go into debug, it goes into the class module I have set up.
The code in this module is below, and the if statement is highlighted
in debug.

Public WithEvents app As Application

Private Sub app_Workbookopen(ByVal Wb As Workbook)

If Wb.Windows(1).Visible = True Then
MsgBox ("True")
End If

End Sub

Does the syntax change if using an add-in rather than PERSONAL.XLS?
Any help would be appreciated.

Thanks,
Rob

 

[Dave Peterson]

Depending on what you're doing, maybe you could just ignore all the addins???

Option Explicit
Public WithEvents app As Application
Private Sub app_Workbookopen(ByVal Wb As Workbook)
If Wb.IsAddin Then
'skip it
Else
If Wb.Windows(1).Visible = True Then
MsgBox "True"
End If
End If
End Sub
Private Sub Workbook_Open()
Set app = Application
End Sub
Private Sub Workbook_Close()
Set app = Nothing
End Sub

 

[RobH]

Ignoring the add in wouldn't help, because I want the add in to be
run. My confusion is that the code works perfectly when run from
PERSONAL.XLS (where we don't want it to be), but falls over when run
from the add-in (where we do want it to be).

Rob

 

[NickHK]

Rob,
Dave's point was that your code probably does not need/want to interact with
add-in, because by their nature, they are different to a "normal" spread
sheet.
For example, with an addin I have:
?workbooks("pdfmaker.xla").name
PDFMaker.xla
?workbooks("pdfmaker.xla").windows(1).Visible
So skip addins (including your own) in your code.
When run from Personal.xls, you did not have any add-ins installed and
consequently never raised this error. Moving the code to an add-in, created
the error.

NickHK

 

[Dave Peterson]

Thanks Nick.

Rob,
Dave's point was that your code probably does not need/want to interact with
add-in, because by their nature, they are different to a "normal" spread
sheet.
For example, with an addin I have:
?workbooks("pdfmaker.xla").name
PDFMaker.xla
?workbooks("pdfmaker.xla").windows(1).Visible

So skip addins (including your own) in your code.
When run from Personal.xls, you did not have any add-ins installed and
consequently never raised this error. Moving the code to an add-in, created
the error.

NickHK