Possible worksheet corruption, any alternative to creating new workbook?

0
votes

I have the following sub in ThisWorkbook:

Private Sub workbook_open()

msgbox ActiveSheet.Name

End Sub

When the workbook opens, the msgbox line gives this error:

Run-time error '32089':
Application-defined or object-defined error

Trying to call the sheet from the Immediate window with:

?Sheets("problemSheetName").Name

also gives the same error. However, other sheets:

?Sheets("someOtherSheet").Name

do not produce this error.

I can't delete this worksheet, because Excel crashes whenever I try to delete it. There is also an ActiveX button on this sheet that crashes Excel when I try to delete it. The button is linked to some code, but it does not run this code when the button is clicked--a breakpoint shows that the code is never executed. Deleting data from cells on the worksheet does not crash Excel.

Is the worksheet just corrupt? I'd prefer not to have to copy the data into another workbook, because this book has 20 sheets, 50 class modules, and several hundred workbook-level named ranges.

EDIT: compiling the VBA gives this error:

Object library invalid or contains references to object definitions
that could not be found
1
vbaexcel
try clearing out your temp files and recompiling the vba – Sorceri
@Sorceri clear temp files from where? – sigil
@Sorceri recompiling the vba gives an error, see edit to question – sigil
Is the problem sheet the first sheet in the workbook? I seem to recall an unresolved bug in VBA where activeX causes an issue trying to select/activate the first sheet in a workbook... – SierraOscar
Another couple of options I have used in the past, (1) select all sheets, right click, move or copy, pick new book (2) open the save the file with OpenOffice. – brettdj

1 Answers

1
votes

I ended up just writing a script to copy all the sheets, modules, and named ranges to a new workbook. This was successful, and did not trigger the crash when the script iterated through the corrupt sheet.