Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reopens After Close
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reopens After Close
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reopens After Close
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. "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reopens After Close
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reopens After Close
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reopens After Close
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 "Chip Pearson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort not saved across saves/reopens | Excel Worksheet Functions | |||
Open New Workbook / Save and Close Current Workbook | Excel Programming | |||
code reopens file | Excel Programming | |||
Closing a workbook from a macro doesn't close the workbook | Excel Programming | |||
Workbook reopens after close | Excel Programming |