Excel file becomes zero bytes

Question

I have Excel files (with many, many macros) deployed to a couple dozen users. I put a new version out to two people and each has had a problem with one of the files becoming zero bytes. This doesn't always happen, just once to each person, so far.

The original design of the software had an Excel file called a "Manager" create "Note" files by copying itself, then deleting the Control Panel tab and unhiding a couple of other tabs in the Note file. This process worked for thousands of files, although every once in a while, the Note file would appear with the Control Panel still present and the other tabs still hidden. For these cases, I believe the file processing stopped in the middle of creating the new Note file.

I redesigned this so that the Manager file creates the Note file by copying a Note file that isn't filled in yet. For these two special cases, each person was able to create the Note file, populate the fields they wanted, then print the file out. Apparently, something happened when the file was saved that caused it to save with zero bytes of content. Any ideas what that might be?

Sorry, I can't post the files, they are proprietary. The code is quite extensive. The following code executes when the file is closed. It makes it so that a blank sheet is activated when closed, so that the sensitive info on the Control Panel isn't shown right away (they have to click on an agreement not to mess with the copyright first).

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ActiveWorkbook.Name = "Manager.xlsm" Then

        With Application.ThisWorkbook

            .Unprotect strWorkbookPassword

            .Sheets("Blank").Visible = True

            .Sheets("Blank").Select

            .Protect strWorkbookPassword

        End With

    End If

End Sub

Moved from: Office / Excel / Windows 10 / Office 365 Home

Answer 1

I put in some code to check for zero-length files, I'm hoping that'll at least allow me to narrow down what step the problem is happening in.