+ Reply to Thread
Results 1 to 4 of 4

Excel crashes / stops working properly after deleting a worksheet

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Newcastle, UK
    MS-Off Ver
    2010
    Posts
    31

    Excel crashes / stops working properly after deleting a worksheet

    I'm currently coming across an incredibly annoying error at the moment - hopefully someone can help me to find a solution, as I can't find anything online!

    I have a workbook, and on the main worksheet ("Main"), I have a "Create Report" button. Clicking this button then runs a macro, which checks if a "Report" worksheet exists (if it does, it is deleted) and then creates a report (the "Report" worksheet).

    When there isn't currently a "Report" worksheet, it generates the report perfectly fine. If I then manually delete the report, and run the macro again, it works perfectly fine.

    The problem comes when the report already exists, so the macro has to delete the Report worksheet and then create the report. From putting stop points in the macro code, and stepping through each line with F8, I've discovered that when the Sheets("Report").Delete line is run, it deletes the worksheet as required, but then the code just stops, as though it's reached the end. It doesn't continue on with the next line. The macro effectively finishes running.

    Following this, Excel either crashes ("Microsoft Office Excel has encountered a problem and needs to close..."), or doesn't properly operate (e.g. normal functions / formulas in cells don't work - for example, if I was to use the SUM() formula on some numerical cells, it would come up as zero regardless of what the 'true' sum was). And then when I go to close Excel, it gives the above error anyway.

    I can't for the life of me work out why I am getting this issue. I have tried countless different methods to try and get round it, for example, a cell with either 1 or 0 in to indicate whether a report already exists, and then the macro checks this cell before attempting to delete the Report worksheet, rather than 'proper' coding to see whether the Reprot worksheet exists.

    Below is the code:

    Please Login or Register  to view this content.
    The macro checks if "Report" exists - if it does, it is deleted. It then copies "Report template" (which is hidden), and renames the new worksheet from "Report template (2)" to "Report". The report is then generated (for the sake of convenience I have not pasted the rest of the macro, plus it's kinda confidential / work-related, so I would rather not!).

    I've even tried using 2 separate buttons - one to check if Report exists and if so deletes it, and one to generate the report. Again, the generate report button works perfectly fine, but the delete report button macro stops running after the .Delete line:

    Please Login or Register  to view this content.
    (This macro uses the separate 0/1 cell for indicating whether a report exists or not - but the same issue arises if I do it the 'proper' way as in the previous code.)

    Any help would be greatly appreciated.

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    1,955

    Re: Excel crashes / stops working properly after deleting a worksheet

    not very sure ...but it seems to be ok for me, if sheet report exists, it's deleted and a new report is created...with text from Report template.
    I am not an expert at all but if you want some help I think the best way is to provide a test file with yr issue.
    Attached Files Attached Files
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  3. #3
    Registered User
    Join Date
    08-26-2009
    Location
    Tucson, AZ
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Excel crashes / stops working properly after deleting a worksheet

    I know this is old, but I am having the exact same issue. I have not ever seen where someone has posted a working answer yet.

  4. #4
    Registered User
    Join Date
    11-07-2012
    Location
    Newcastle, UK
    MS-Off Ver
    2010
    Posts
    31

    Re: Excel crashes / stops working properly after deleting a worksheet

    Do you have any buttons on the worksheet you're trying to delete?

    I found that if I have 'ActiveX' controls (the ones you can customise the appearance of, e.g. background colour, font colour, etc.) on my worksheet then it would often stop the macro from running immediately after deleting the worksheet, whereas if I have 'Form' controls then it deletes without any issues. I never found a solution to this, so I had to make do with the more basic looking buttons on any sheets I would potentially want to be able to delete with a macro.

    Also, I improved my code. Here it is in its existing form:

    Please Login or Register  to view this content.
    If a "Report" worksheet exists, the user faces a popup asking if they wish to overwrite the existing report. If they choose no, the macro ends, if they choose yes, the existing macro continues to run (the "Report" worksheet is deleted and a new one based on the template is generated).

    This code isn't probably as 'clean' as it could be, but it gets the job done for me reasonably quickly, so I'm happy with it. You may want to make some alterations, for example, the "10" in "Sheets(10)" can be changed to modify the positioning of the worksheet.
    Last edited by Pedsdude; 12-02-2014 at 05:14 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1