Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete value on worksheet 2 that match values on worksheet 1 | Excel Discussion (Misc queries) | |||
delete worksheet | Excel Worksheet Functions | |||
How to delete a duplicate Excel worksheet within that worksheet? | Excel Discussion (Misc queries) | |||
Delete a worksheet | Excel Discussion (Misc queries) | |||
WorkSheet.Delete | Excel Programming |