Error deleting worksheet (Excel 2003)

  • Thread starter Thread starter cr113
  • Start date Start date
C

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?
 
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)
 
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
Click to expand...

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!
 
Back
Top