How to delete a worksheet from running code

[ElCaito]

Hi.
I am new to the forums.

How is it possible to delete the worksheet that has the actual code running from it?

I have three worksheets, where one is "MAIN" and it has the ActiveX command button to fire up the code I've got. When the program is about to complete, it attempts to delete this worksheet along with another.

It deletes another worksheet, no problem, but when I try to delete the "MAIN" worksheet, which has the code, I get this an "Automation" error, saying Run Time error "-2147221080".

Is there a way to force a command button to run code from another location?

Thanks in advance.

[Bob Phillips]

Hi.
I am new to the forums.

How is it possible to delete the worksheet that has the actual code running from it?

I have three worksheets, where one is "MAIN" and it has the ActiveX command button to fire up the code I've got. When the program is about to complete, it attempts to delete this worksheet along with another.

It deletes another worksheet, no problem, but when I try to delete the "MAIN" worksheet, which has the code, I get this an "Automation" error, saying Run Time error "-2147221080".

Is there a way to force a command button to run code from another location?

Thanks in advance.

You could use the OnTime method.

I just knocked up a test to demonstrate it. I added this worksheet activate code to the worksheet code module

[vba]
Private Sub Worksheet_Activate()
MsgBox "hello"
Application.OnTime Now + TimeSerial(0, 0, 5), "DeleteSheet"
MsgBox "goodbye"
End Sub
[/vba]

and this to a standard code module

[vba]
Sub DeleteSheet()
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub
[/vba]

activating the sheet showed the two messages fine, then deleted the sheet 5 seconds later.

[ElCaito]

xld,

I am trying the code out. I think I know what you are trying to say. I'll get back to you soon.

Eres Chileno?

Yo tambien!!

jejeje

chao

[ElCaito]

Ok amigo. It worked!!!

Thank you. That's exactly what I needed.

It's a shame that Excel doesn't allow you to bind a vb module other than the worksheet code to an ActiveX control directly though.

chao