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:

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

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:

[code]
Option Explicit
public bln as boolean

write the public deklaration in a Standard module.

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

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

Hi Melanie,

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

Any other ideas?

Alex

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)

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



-----Original Message-----
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)

.