PDA

View Full Version : How to delete a worksheet from running code



ElCaito
12-13-2005, 10:16 PM
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
12-14-2005, 03:12 AM
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


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


and this to a standard code module


Sub DeleteSheet()
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub


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

ElCaito
12-14-2005, 03:51 PM
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
12-14-2005, 04:01 PM
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