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, procedu="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" wrote in message
...
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
"Gary''s Student" wrote:
Are you using any OnTime events??
--
Gary''s Student - gsnu200787
"owp^3" wrote:
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?", Structu=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