BeforeClose code working in debug but sporadically in actual execution - how can I test it

I hate asking this question as I fear it will get no replies but I'm completely stumped. I have some code that runs BeforeClose based on a user selection from a user form. It cleans up the entire workbook to delete any unused rows or formulas that are hidden or result in blank values. That works exactly as expected (the clean up code) but it's unpredictable when actually running before close.

Every time I try to debug it using the ways I'm familiar with (stepping through the code, breaking out and testing pieces of code) it works fine. I get no error message when it doesn't work, I just reopen the file and the rows that should have been deleted weren't. When I try it a second time it works fine.

My only guess at this point is that I have some other code that's disabling it somehow, but I sure thought I'd notice that when doing a debug...

My second issue is that my code is complicated and messy because I'm still an apprentice Excel coder at best and I fear an expert will take one look at it and say "I can't work with this - it's too crazy" - so I'm sharing what I THINK you may need to help, but I expect you may need more detail o kindhearted soul who's willing to give this a look!

Option Explicit 'require all variables to be declared
Public wb1 As Object 'make wb1 a public variable for use in any sheet or sub in this module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

bCancelClose = False 'reset the bCancelClose variable since "cancel" may have been previously selected

On Error GoTo ErrorHandler

If bDoNotRun = False Then 'If yes isn't selected in the message box in the "cancel" case, continue with the BeforeClose code
   
    Application.OnKey "{Up}" 'return the up key to normal function
    Application.OnKey "{Left}" 'return the left key to normal function

    If WorksheetExists("Quick Import Mapping") Then
        If Sheets("Location").Range("K2").Value = vbNullString Or Sheets("Location").Range("K2") = "NA" Then 'check to see if the file is in Order stage and bypass the below code if so
            If Sheets("Quick Import Mapping").Range("A13") <> vbNullString And (Sheets("Access").Range("A13") <> vbNullString Or Sheets("CPE").Range("A13") <> vbNullString) Then 'check to see if the user entered both Quick Import data AND other data and warn them
                MsgBox "Your worksheet contains both Quick Import and Bulk Upload data." & vbNewLine & vbNewLine & _
                "Please remove data from the Quick Import Mapping sheet OR the Product sheets before saving for upload."
                Cancel = True 'Cancel the close to allow the user to delete the conflicting data
                Exit Sub
            End If
        End If
    End If

    UserForm1.Show 'show the workbook close options window/userform
    If bCancelClose = True Then 'if the bCancelClose variable has been set to True via the UserForm code, cancel the close process
        Cancel = True
    End If
    Exit Sub
    
ErrorHandler:
    MsgBox "Description: " & Err.Description & vbNewLine & _
        "Error Number: " & Err.Number & vbNewLine & _
        "Error at Line: " & Erl & vbNewLine & _
        "On sheet: " & ActiveSheet.Name & vbNewLine & _
        "Please report this error information to the Bulk Import & Export in ProQuest Chatter group" & vbNewLine & _
        "Include a screen shot if possible."
    Resume Next

End If
End Sub

Private Sub SaveforUpload_Button_Click()
    CleanUpForUpload
    ActiveWorkbook.Save
    Unload Me
End Sub

Private Sub SaveforEditing_Button_Click()
    ActiveWorkbook.Save
    Unload Me
End Sub

Private Sub CloseNoSave_Button_Click()
    Unload Me
    Call SetDoNotRun 'If the user chooses to close without saving, this bypasses the BeforeClose code so they aren't just asked again
    ThisWorkbook.Close savechanges:=False
End Sub

Private Sub CancelClose_Button_Click()
    Unload Me
    bCancelClose = True
End Sub

'------------------------------------------------------------------------------------------------
' Clean up the workbook for upload to PQ - delete all unused rows in all tabs containing XML code
'------------------------------------------------------------------------------------------------
Sub CleanUpForUpload()
' Turn off some Excel functionality so code runs faster
'  called BeforeWorkbookClose in ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim ws As Worksheet

On Error GoTo ErrorHandler

If Application.WorksheetFunction.CountA(Worksheets("Location").Range("A13:A413")) = 0 Then
    GoTo BypassClean
End If

    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name Like "_*" And ws.Name <> "Help" And ws.Name <> "Product Selection" And ws.Name <> "Private IP QoS Profiles" And ws.Name <> "Site Questionnaire" And ws.Name <> "EVC Configurations" And ws.Name <> "Order Details" Then
            If ws.Range("A13").Value <> vbNullString And ws.Visible = xlSheetHidden Then
                UserForm1.Hide
                MsgBox ws.Name & " is hidden but contains data." & vbNewLine & _
                "Please select the tab in the Product Selection screen" & vbNewLine & _
                "or delete the Quote Location Name(s) on that tab to continue", vbOKOnly
                ws.Visible = xlSheetVisible
                bCancelClose = True
                GoTo BypassClean
            End If
            DeleteUnusedRows ws 'call the DeleteUnusedRows Sub passing the Worksheet name as "ws"
            DeleteBlankCells ws 'call the DeleteBlankCells Sub passing the Worksheet name as "ws"
        End If
    Next ws

BypassClean:
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub

ErrorHandler:
MsgBox "Description: " & Err.Description & vbNewLine & _
        "Error Number: " & Err.Number & vbNewLine & _
        "Error at Line: " & Erl & vbNewLine & _
        "On sheet: " & ws.Name & vbNewLine & _
        "Please report this error information to the Bulk Import & Export in ProQuest Chatter group" & vbNewLine & _
        "Include a screen shot if possible."
Resume Next

End Sub

'---------------------------------------------------------------------------------------------
' Delete all blank rows - called from CleanUpForUpload
'---------------------------------------------------------------------------------------------
Sub DeleteUnusedRows(ws As Worksheet)
    With ws
        On Error Resume Next
        ws.UsedRange.Offset(12).Columns(1).SpecialCells(4).EntireRow.Delete
        On Error GoTo 0
    End With
End Sub

'---------------------------------------------------------------------------------------------
' Delete all blank cells - called from CleanUpForUpload
'---------------------------------------------------------------------------------------------
Sub DeleteBlankCells(ws As Worksheet)
' This code actually copies all of the non-null cells (user-entered or a formula
'   that evaluates to something other than "") and pastes it as a "clean" value
'   that does not contain any non-printable characters - this effectively removes
'   any formulas that PQ would see as actual values unintentionally

Dim Rng As Range
    With ws
        On Error Resume Next
        Set Rng = Application.Intersect(.UsedRange, .Cells.Resize(.Rows.Count - 12).Offset(12))
        Rng.Value2 = ws.Evaluate("IF(ROW(" & Rng.Address & "),CLEAN(" & Rng.Address & "))")
        On Error GoTo 0
    End With
End Sub

Function SetDoNotRun() 'Code to set the value for the trigger that keeps the BeforeClose code from running (see ThisWorkbook)
    bDoNotRun = True
End Function

Function SetCancelClose() 'Code to set the value for the trigger that allows the user to cancel the save and close (see UserForm1)
    bCancelClose = True
End Function

If you made it through all of that without giving up, any guess why it would work sometimes and not others, or any suggestions on how to test it differently to find the problem myself?