Hi Guys

I have created an AD-in in Excel 2002 (win 2k). It works
fine in Excel format but when I convert it to Add-in I run
into problems.

The following userform event errors out (Run time 91,
obeject variable or with block variable not set)
It happens only when Know other work book is open and Add-
in is open on its own.
Is there a way to ceate a new workbook if know other
workbook is present , select it so that it does not
encounter the error.

Thanks a lot

Private Sub UserForm_Initialize()


Dim WB As Workbook
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
Me.ListBox1.AddItem WB.Name
End If
Next WB


End Sub

use this code to trap that error

If ActiveSheet Is Nothing Then Exit Sub


Cesar Zapata




"James" wrote in message
...
Hi Guys

I have created an AD-in in Excel 2002 (win 2k). It works
fine in Excel format but when I convert it to Add-in I run
into problems.

The following userform event errors out (Run time 91,
obeject variable or with block variable not set)
It happens only when Know other work book is open and Add-
in is open on its own.
Is there a way to ceate a new workbook if know other
workbook is present , select it so that it does not
encounter the error.

Thanks a lot

Private Sub UserForm_Initialize()


Dim WB As Workbook
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
Me.ListBox1.AddItem WB.Name
End If
Next WB


End Sub

Thanks Cesar
How can I add a new sheet from with Add-in if I want to
Thanks
-----Original Message-----
use this code to trap that error

If ActiveSheet Is Nothing Then Exit Sub


Cesar Zapata




"James" wrote in
message
...
Hi Guys

I have created an AD-in in Excel 2002 (win 2k). It works
fine in Excel format but when I convert it to Add-in I
run
into problems.

The following userform event errors out (Run time 91,
obeject variable or with block variable not set)
It happens only when Know other work book is open and
Add-
in is open on its own.
Is there a way to ceate a new workbook if know other
workbook is present , select it so that it does not
encounter the error.

Thanks a lot

Private Sub UserForm_Initialize()


Dim WB As Workbook
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
Me.ListBox1.AddItem WB.Name
End If
Next WB


End Sub




.

I dont understand your question but try this


Sheets("Sheet1").select
Sheets.Add

use the macro recorder to get the code then change it to your needs.



Cesar.



"James" wrote in message
...
Thanks Cesar
How can I add a new sheet from with Add-in if I want to
Thanks
-----Original Message-----
use this code to trap that error

If ActiveSheet Is Nothing Then Exit Sub


Cesar Zapata




"James" wrote in
message
...
Hi Guys

I have created an AD-in in Excel 2002 (win 2k). It works
fine in Excel format but when I convert it to Add-in I
run
into problems.

The following userform event errors out (Run time 91,
obeject variable or with block variable not set)
It happens only when Know other work book is open and
Add-
in is open on its own.
Is there a way to ceate a new workbook if know other
workbook is present , select it so that it does not
encounter the error.

Thanks a lot

Private Sub UserForm_Initialize()


Dim WB As Workbook
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
Me.ListBox1.AddItem WB.Name
End If
Next WB


End Sub




.

Hi James,

Your users may not appreciate having new workbooks opened just so that
your add-in doesn't generate an error :)

How about using the Count property of the Workbooks collection to
determine the number of open workbooks and then proceed with the rest
of your routine if it is appropriate to do so.

HTH
Richie

"James" wrote in message ...
Hi Guys

I have created an AD-in in Excel 2002 (win 2k). It works
fine in Excel format but when I convert it to Add-in I run
into problems.

The following userform event errors out (Run time 91,
obeject variable or with block variable not set)
It happens only when Know other work book is open and Add-
in is open on its own.
Is there a way to ceate a new workbook if know other
workbook is present , select it so that it does not
encounter the error.

Thanks a lot

Private Sub UserForm_Initialize()


Dim WB As Workbook
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
Me.ListBox1.AddItem WB.Name
End If
Next WB


End Sub