ExcelBanter - View Single Post - Deleting worksheets containing ActiveX controls

Alex[_24_]

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 

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