Error deleting worksheet (Excel 2003)

[cr113]

I'm getting the following error: "Run time error -2147221080:
Automation error" running an excel macro in 2003. I think the problem
is when I try to delete a worksheet with a command button on it. I
think somehow the button creates a reference to the worksheet and this
causes a problem when I delete the worksheet.

To duplicate this error add a button to the first worksheet and step
thru the following code using F8:

sub test
Worksheets(1).Delete
end sub

After deleting the worksheet I get the following message: "Can't enter
break mode at this time"

I think this is leading to the Automation error.

Any workarounds?

 

[Chip Pearson]

Does the button that starts the delete process reside on the same
sheet that is being deleted? That could cause a problem. You can use
Application.OnTime to delete that sheet, and the code execution will
be deferred until Excel is ready to do it. E.g,

'[in the sheet's code module]
Sub Button1_Click()
Application.OnTime Now,"DeleteSheet",,True
End Sub

'[in a regular code module]
Sub DeleteSheet()
Application.DisplayAlerts = False
Worksheets(1).Delete
Application.DisplayAlerts = True
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[cr113]

Does the button that starts the delete process reside on the same
sheet that is being deleted? That could cause a problem. You can use
Application.OnTime to delete that sheet, and the code execution will
be deferred until Excel is ready to do it. E.g,

'[in the sheet's code module]
Sub Button1_Click()
        Application.OnTime Now,"DeleteSheet",,True
End Sub

'[in a regular code module]
Sub DeleteSheet()
        Application.DisplayAlerts = False
        Worksheets(1).Delete
        Application.DisplayAlerts = True
End Sub

I think you're right. I think the reason I was getting an error was
because I was trying to delete a worksheet that had remaining code
left to execute. I fixed it by moving the remaining code to a code
module but it sounds like the "OnTime" method is the way to go.

Thanks!