Corrupted file information

I want to relate my experience and the good final outcome for the benefit of others

I recently had a situation when modifying a fairly complex spreadsheet with after making changes on the next open I was faced with the "your file is damaged, repair it yes or no". If you say no the file just closes, so the only real answer is yes. In my situation the "repaired" file was beyond use. For example all of the Tables I had been using were no longer tables, and any formula columns in nthem were lost. I had two tables that were updated by power query downloads and these tables were then accessed by a number of pivot tables. The changes I was making involved some pretty complex formulas with sumifs, averageifs etc. So lots of possibilities that might cause this problem.

One possible cause was a malfunction of my storage, or a MAC vs WIN issue. But using a WIN computer put that to rest.

I then went through a stepwise process to make the changes, and saving after each type of change as a different file. On stage 9 I discovered the cause. I had a private sub worksheet_open() on the worksheet I was changing. It was there to Protect the worksheet when opened so casual users would not be able to mistakenly overwrite formulas. The final changes involved accessing a different worksheet. I became tired of unprotecting the worksheet every-time so I commented out the Protect VBA code. It was the only line in the worksheet_open. But I just commented out the one line of vba code, not the whole sub. That was the problem. Having the worksheet_open(), but then no active code was resulting in the file being flagged as needing repair. Of course if you make a coding problem when setting up vba like this you get an error code as you enter it. But if you comment out the one and only line of code in the workbook_open you will get this problem.

I certainly would never have expected this to happen and it was only after I switched from "frustrated" to "investigative" mode that I was able to decipher it.

I hope this helps someone because an extensive search of the internet gave me no clue in this direction.