Delete Worksheet with VBA
 

Hello,

I have following problem:
I created an ActiveX in Visual Basic 6 which has only one public integer
variable "x" (no other variables or functions)
----------------------------------------
Public x As Integer
----------------------------------------

In Excel the workbook only has one worksheet. I added two vba buttons to
this worksheet. The first button calls a macro which copies the worksheet and
calls the method "ThisWorkbook.OnAdd":
----------------------------------------
' copy worksheet
Private Sub CommandButton1_Click()
Dim Table As Worksheet
Set Table = ThisWorkbook.Sheets("Table1")
Table.Copy after:=ThisWorkbook.Worksheets(Worksheets.Count) ' add worksheet
ThisWorkbook.OnAdd
End Sub
----------------------------------------

The second button removes the before inserted worksheet by calling the
method "ThisWorkbook.OnDel":
----------------------------------------
Private Sub CommandButton2_Click()
ThisWorkbook.OnDel
End Sub
----------------------------------------

The code in this workbook looks like this:
----------------------------------------
Option Explicit
Public pro As MyAX.MyClass

Private Sub Workbook_Open()
Set pro = New MyAX.MyClass
pro.x = 3
End Sub

Public Sub OnAdd()
Dim val
val = pro.x
End Sub

Public Sub OnDel()
Sheets(ThisWorkbook.Worksheets.Count).Delete
End Sub
----------------------------------------

When the workbook becomes opened "Workbook_Open" is called and "pro" is set
to "New MyAX.MyClass" (my ActiveX project). Afterwards the value 3 is
assigned to pro.x. Then I hit the first button to copy the worksheet. The
created worksheet appears and "OnAdd" from Thisworkbook is called. "pro.x"
still has the value "3". I activate the first worksheet and hit the second
button to delete the previously created worksheet. After that I hit the first
button again to copy the worksheet again. Again a worksheet appears and
"OnAdd" is called - BUT NOW "pro" does not exist anymore!!!
WHY???
When I delete the worksheet without the vba script (with right mouse menu
and delete), and add again a worksheet "pro" does still exist.
I also tried Visual C++ and C# instead of Visual Basic to create an ActiveX
(or Class Library) but I always had the same problem.
What can I do that "pro" isn't deleted?

Please help!
Thanks
Josef