Excel crashes when selecting tab on bottom

[ABabeNChrist]

I have a workbook .xlsm that I use to do various reports. As I work on the reports I navigate to various sheets using the tabs on the bottom and for some unknown reason excel seems to crashes 50+% of the time when selecting the sheet tab named Summary on the bottom and this does not happen when selecting any other sheets. I would attach a copy of workbook, but it’s too big. Any thoughts what I might look for?

[Rudi]

It's difficult to pinpoint the exact cause but what you could try is the following:

Add a new sheet to the workbook
Select the used range of data (A1: Last used cell) on the Summary sheet and copy
(Do not just duplicate the entire sheet by using Move or Copy -- you only want to copy the actual data)
Select the new sheet, click in A1 and paste
Delete the old Summary sheet
Rename the new sheet to Summary
Save and close the workbook
Reopen and test if the issue is still present (or not :))

[ABabeNChrist]

Good idea thanks, it ran across my mind, worth a try

[ABabeNChrist]

No that didn't work still crashed. It only happens when i select the Summary tab

Untitled.jpg

I think i will try using

Code: Select all

Sheets(Summary).Select

and see if this help at all

[Rudi]

It sounds like the Summary tab is corrupt?

A few other points to try:

Do you have any objects like many charts or shapes on the sheet. (Note that shapes can be invisible too so the best way to check for this is using the Selection Pane in the Find and Select button on the Home ribbon). If there are unnecessary shapes, delete them.

Try saving the workbook as *.xlsb or *.xls (if you do not have more than 65536 rows of data)

Try using the Open and Repair option from the Open dialog. Navigate to your problem file, select it by clicking once on it, then use the drop down arrow to choose Open and Repair...

Image 027.png

Open Excel in safe mode and see if the problem still persists on the file. Safe mode prevents any add-ins and startup macros from firing and if the workbook ( and sheet) work without crashing in safe mode, this points to things like add-ins or macros that is causing the sheet to crash. You may want to then visit the add-ins and macros to disable those that could be the cause.
To start Excel in safe mode, open Excel while holding down the CTRL button.

If a workbook (or a worksheet) has gone completely corrupt, and you cannot access it via Excel, then I use Apache Open Office to open the workbook. I would save as a *.xls from this application or do copy/paste of worksheets in here, save it and then open in Excel again to save back in *.xlsx format. (This is a last resort though)

[ABabeNChrist]

The workbook is a .xlsm wont that affect the existing VBA code

I was playing around trying to use vba to open the Summary sheet

Code: Select all

    With Worksheets("Summary")
        .Visible = xlSheetVisible
        .Activate
    End With

even though the sheet is not hidden, so far this seems to work (fingers crossed)

[Rudi]

Holding thumbs...

BTW:
Besides the last point, using Open Office, none of the others will affect your code.
For the last point, just copy your code to notepad (or export the modules) as backup. Apply the fix, and then import the code back into the workbook.

[ABabeNChrist]

I completed 4 reports using only the code below to navigate to the Summary sheet.

Code: Select all

    With Worksheets("Summary")
        .Visible = xlSheetVisible
        .Activate
    End With

While for the most part this seemed to work, I still had moments when the workbook would still crash when navigating to the Summary sheet. There was something else I noticed, it seemed that it only occurred after I selected a save button I created (code used below), could the save workbook still be cycling though possibly cause this.

Code: Select all

    ThisWorkbook.Save