Deleting worksheets containing ActiveX controls

[Alex]

I am having trouble deleting worksheets containing ActiveX
controls whilst maintaining the values in publicly
declared variables.
EG.

Create two worksheets, each with two command buttons on.
On Sheet2, put in the following VBA code:

Option Explicit
public bln as boolean

Private Sub CommandButton1_Click
bln = True
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub

Private Sub CommandButton2_Click
msgbox bln
End Sub

Now exit design mode and try clicking button 1 followed by
button 2. It SHOULD display 'TRUE', but it actually
displays 'FALSE' as the publicly declared variable bln has
been cleared.

Anyone have any ideas?

Thanks,

alex

 

[Melanie Breden]

Hi Alex,

I am having trouble deleting worksheets containing ActiveX
controls whilst maintaining the values in publicly
declared variables.
EG.

Create two worksheets, each with two command buttons on.
On Sheet2, put in the following VBA code:

Option Explicit
public bln as boolean[/QUOTE]

write the public deklaration in a Standard module.

--
Regards

Melanie Breden
- Microsoft  MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Alex]

Hi Melanie,

I have tried that aswell, it still doesn't work
unfortunately.

Any other ideas?

Alex

 

[Melanie Breden]

Hi Alex,

I have tried that aswell, it still doesn't work unfortunately.

Any other ideas?

I tested the behavior from XL97 to XL2003 with the same result.

It is unbelievable:
With delete from ActiveX controls global variables lose their value
after the calling procedure is terminated.
I think, this is a Bug :-(

Public bln As Boolean
Public str As String

Sub DeleteActiveX()
bln = True
strText = "Melanie"
ActiveSheet.DrawingObjects.Delete
MyTest
End Sub

Sub MyTest()
MsgBox bln
MsgBox strText
End Sub

Sub NewTest()
MsgBox bln
MsgBox strText
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Guest]

Thanks Melanie,

In some ways I'm glad you're getting the same results as
me - at least its not mean doing something silly.
Somewhat unfortunate if it is a bug, but there's not a lot
I can do about it I guess. Not sure how I can even contact
Microsoft support given the fact they seem to only be
willing to speak to you if you have paid for support
(despite it being a problem with their software!).
Thanks for your help,

alex