Workbook Reopens After Close

[owp^3]

I am having a strange problem with a workbook that has some userforms.

The workbook opens by itself about 5 seconds after I close it. This only
happens when I choose not to save it. It stays closed when I save and it
stays closed when I exit whether I save or not.

I can't figure it out.
Any help would be appreciated!
owp^3

PS: I am using 2003 and here are the relevant subs in the ThisWorkbook object.

Private Sub Workbook_Activate()

ActiveWorkbook.Unprotect "Wh4tIf?"
ActiveSheet.Unprotect "Wh4tIf?"
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = False

With Application
.ShowStartupDialog = False
.DisplayFormulaBar = False
End With

With ActiveWindow
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
End With

ActiveWorkbook.Protect Password:="Wh4tIf?", Structure:=True, Windows:=True

For Each ws In Sheets
ws.Unprotect "Wh4tIf?"
Next ws

ActiveSheet.Protect Password:="Wh4tIf?", UserInterfaceOnly:=True,
DrawingObjects:=False, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Application.ScreenUpdating = True

frm_Splash.Show

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
TitleText = "Thank YOU! Please return this survey to MKT_Project_Desk"
ThankYouMsg = "Thank you for participating in this Impacts &
Approaches survey." & vbCrLf & _
"Please eMail this workbook to: Mktg_Project_Desk" & vbCrLf
& _
"The Marketing PMO Team" & vbCrLf & vbCrLf '& _

Result = MsgBox(ThankYouMsg, vbOKOnly, TitleText)

End Sub

Private Sub Workbook_Deactivate()
ProtectionToggle
Application.ScreenUpdating = False
With ActiveWindow
.DisplayHeadings = True
.DisplayOutline = True
.DisplayZeros = True
End With
With Application
.ShowStartupDialog = True
.DisplayFormulaBar = True
End With
Application.ScreenUpdating = True
ProtectionToggle
End Sub

 

[Gary''s Student]

Are you using any OnTime events??

 

[owp^3]

Yes, the frm_Splash, which is called in the Workbook_Activate sub has a 15
second OnTime event. The OnTime event unloads the Splash Form in the event
the user doesn't close it themselves.

I will comment it out and see if it is the culprit.

I am pretty sure that is the only one. For some reason my Find function
won't popup when called so I haven't been able to search for it to make sure.

 

[owp^3]

Yup, that was it. So how can I have the form "time out" without having this
re-open issue?

Forms
frm_Splash
Private Sub UserForm_Activate()
Const SS_DURATION As Long = 15 'seconds
Application.OnTime Now + TimeSerial(0, 0, SS_DURATION), "KillTheSplash"
End Sub

Modules
Functions
Public Sub KillTheSplash()
Unload frm_Splash
End Sub

 

[Chip Pearson]

You can cancel a pending OnTime event by calling OnTime using the *exact*
same value for the time and set the final parameter to False. Since the
cancel time must be exactly the same as the schedule time, you need declare
a Double type variable at the module level, set that variable to the time to
run. Then, in your workbook close event, call OnTime to cancel the pending
timer (if any).

On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:="TheProc", _
Schedule:=False

where RunWhen is a Double variable containing the time that you used to set
up the OnTime.

See http://www.cpearson.com/excel/ontime.aspx for more details.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[owp^3]

Thanks Stu & Chip,
I have disabled OnTime for now.
I don't have the time to get it to work right.
So I will save that for another day.
owp^3