excel not closing correctly - userform hanging?

Question

I have a spreadsheet with 6 userforms, used on about 30 computers. The VBA code is password protected. Often when we close the sheet, the VBA project password box appears and excel.exe remains in task manager.

I have done a bit of testing and come up with the following:

The problem only occurs when a userform has been opened.
Nothing needs to be done with the userform to cause the popup other than to press Cancel (which calls Unload Me)

The Workbook_BeforeClose event is as follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ScreenUpdating = False

'Cancel autosave
Sheets("BOH General").Range("A102").Value = 0
AutoSaveTimer

'Application.EnableEvents = False

If Not Sheets("START").Visible = True Then Call CostingMode
Call BackItUp

'Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

And here are some other macros called by Workbook_BeforeClose:

Sub AutoSaveTimer()

If Sheets("BOH General").Range("A102").Value > 0 Then
    RunWhen = Now + TimeSerial(0, Sheets("BOH General").Range("A102").Value, 0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoSaveIt", _
        Schedule:=True
Else
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoSaveIt", _
        Schedule:=False
    On Error GoTo 0
End If

End Sub

Sub AutoSaveIt()
ThisWorkbook.Save
Call AutoSaveTimer
End Sub

Sub BackItUp()

'Dont run if unsaved
If Sheets("BOH General").Range("A111").Value = "" Then Exit Sub

'Prompt
If MsgBox("Do you want to backup this sheet now (recommended if you made any changes)?", vbYesNo) = vbNo Then Exit Sub

'reformat date
Dim DateStamp As String
DateStamp = Format(Now(), "yyyy-mm-dd hh-mm-ss")

On Error Resume Next
MkDir ActiveWorkbook.Path & "\" & "Backup"
On Error GoTo 0

ActiveWorkbook.SaveCopyAs (ActiveWorkbook.Path & "\" & "Backup" & "\" & ActiveWorkbook.Name & " - backup " & DateStamp & ".xlsb")

ActiveWorkbook.Save

End Sub

Is this a userform error, is the userform not closing properly? Or is it something else?

UPDATE: This error only occurs after the user clicks the excel close button (top right) clicking File>Close does not produce the error.

I am pretty sure that Excel cannot truly and completely close if there is an .OnTime() event queued. — Excel Hero
@ExcelHero, The code is supposed to stop the ontime event before the sheet closes. In any case, while I was doing the testing i hadn't enabled the autosave function — user2967539
If you're unfamiliar then it's highly unlikely you're using it but the personal workbook is a hidden workbook that is typically used to hold macros the user wants to apply application wide. You can read more about it here and here. Edit: Btw, I would go with what @excelhero has to say. He's far better than I ^_^; — findwindow

ZatMan ZatMan · Accepted Answer · 2015-09-25T12:06:24

Interesting that I have been experiencing the same instance, it has only cropped up recently as well. Did a version of MOS change this behavior? I have users on Excel 12.0.6611.1000 and 12.0.6712.5000 that do not get this error, on 12.0.6729.5000 it always occurs.

Edit; I resolved this issue on my end today after discovering that several users had 'Drop Box' installed. Uninstalling or turning off drop box just prior to closing the application resolved the issue.

excel not closing correctly - userform hanging?

1 Answers