A bug in Excel???
 

Hello,

I created a workbook with one worksheet. In that worksheet I inserted two
buttons. When I hit the first button a methode is called which copies the
worksheet and inserts the new one. When I hit the second button the inserted
worksheet is removed.
The workbook has a variable x as integer. When the workbook is opened x is
set to the value 3. When the first worksheet is copied, x still has the value
of 3. When I remove the inserted worksheet and add it again with the help of
the buttons, x is 0 (!!!). When I remove the inserted worksheet with the
right mouse button and the appearing context menu, x still has the value 3.
Can anyone tell me the reason why the global x is deleted when removing the
worksheet from VBA?

Here is the tutorial:
1. Create a Excel workbook with on worksheet.
2. Add two buttons to the worksheet
3. Add following methodes to the workbook:
------------------
Option Explicit

Private x As Integer

Private Sub Workbook_Open()
x = 3
End Sub

Public Sub OnAdd()
Debug.Print x
End Sub

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

4. Add following methodes to the worksheet:
------------------
Option Explicit

Private Sub CommandButton1_Click()
Sheets(Worksheets.Count).Copy after:=Sheets(Worksheets.Count)

ThisWorkbook.OnAdd
End Sub

Private Sub CommandButton2_Click()
ThisWorkbook.OnDel
End Sub
------------------

Take a look on the output in the debug window for the value of x. Does
anyone know a workaround so that x will not be deleted?

Thanks
Josef