Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error deleting worksheet (Excel 2003)
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error deleting worksheet (Excel 2003)
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) On Mon, 27 Oct 2008 13:10:19 -0700 (PDT), cr113 wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error deleting worksheet (Excel 2003)
On Oct 27, 4:19*pm, Chip Pearson wrote:
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Worksheet in excel = 2003 / 2007 | Excel Worksheet Functions | |||
Excel 2003 converted to 2007 copying worksheet error | Excel Discussion (Misc queries) | |||
-2147417848 Error Code - Deleting a Worksheet using VBA | Excel Programming | |||
Deleting Worksheet Error | Excel Programming | |||
error runtime 9: deleting a worksheet | Excel Programming |