2

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:

  1. The problem only occurs when a userform has been opened.
  2. 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.

Improve this question
10
  • Completely random guess but personal.xlsb perhaps? Commented Sep 10, 2015 at 22:43
  • 1
    I am pretty sure that Excel cannot truly and completely close if there is an .OnTime() event queued. Commented Sep 10, 2015 at 22:44
  • @findwindow, can you please expand on that comment? Commented Sep 10, 2015 at 22:53
  • @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 Commented Sep 10, 2015 at 22:54
  • 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 ^_^; Commented Sep 10, 2015 at 22:55

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Finally, that's it!! Is there a method of searching for a conflict between 2 programs like this?
It's been a while, but I opened a Ticket with Drop Box and they have acknowledged that their software is the culprit. Unfortunately they seem to be taking their sweet time fixing it; Opened Ticket on Drop Box Website
Congrats for this answer worked for me. The problem can be very easily demonstrated. A new workbook with just 1 button that calls a 2 line macro that loads and shows a userform with just a Cancel Button with Unload me .. and of course password protection on the VBA project. Thanks - saved me hrs.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.