07 Wb opened w/ 03 and it Reopens after Close

Closed
Josh - May 11, 2012 at 08:23 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 9, 2012 at 07:47 PM
Hello,
Well, I am back after my short-lived exhuberance with my other solution. I now have a problem with some of the users that have Excel 2003. I created everything, including their workbooks, in 2007. For some reason, my code works just fine to close the workbook after some time, but for some reason the file reopens. This causes an issue because my main workbook tries to pull data from those workbooks and it can't do that when they are open. The code that I used is below.

This is in ThisWorkbook
-----------------------------------------------------------------------------------------------------
Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
ResetTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub
------------------------------------------------------------------------------------------------------
This is in the module
------------------------------------------------------------------------------------------------------

Public CloseDownTime As Variant

Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:30:00") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub

Public Sub CloseDownFile()
On Error Resume Next
Application.StatusBar = "Inactive File Closed: " & ThisWorkbook.Name
ThisWorkbook.Close SaveChanges:=True
End Sub
------------------------------------------------------------------------------------------------------

Any assistance would be appreciated. This has got me stumped.

Thank you,
Josh


6 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 11, 2012 at 05:59 PM
0
rizvisa1:
Thanks for the link and it's a great reference for creating OnTime events, however I'm still lost as to how the folder reopens on its how.

Thank you,
Josh
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2012 at 05:23 PM
what is this "CloseDownTime" ?
0
rizvisa1:

To be honest, I don't really know. Shows how green I am with coding. In my head, if it doesn't behave like this for excel 07 users, why does it behave like this for excel 03 users?

Any insight would be appreciated.

Thank you,
Josh
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 17, 2012 at 06:29 PM
There are numerous reference to this in your code
I did not spot any place where this is being set
0
Is it being set in this line?

CloseDownTime = Now + TimeValue("00:30:00") ' hh:mm:ss

Could it be that this is being set at the wrong time? Shoudl it be set sooner?

Thank you,
Josh
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 26, 2012 at 07:47 AM
That line i did miss.

But i was confused over this
f Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime,

Is it possible to post a sample workbook with complete macro's at some file share site and posting back link to the file back here
0
rizvisa1:

The code I provided is the entire code. If you think providing a workbook with it in there, I can upload it. Let me know.

Thank you,
Josh
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 30, 2012 at 03:14 AM
I am confused over the three events that you have coded for . What is your intent here for each event?
0

Didn't find the answer you are looking for?

Ask a question
Josh, did you manage to work out how to resolve this workbook re-opening issue? I'm experiencing this as well and can't quite figure it out.

Thanks
0
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012
Oct 8, 2012 at 07:59 AM
y.pango:

I did not find the solution for it. I've tried a few places to figure it out and everyone is at a loss.
Sorry,
Josh
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 9, 2012 at 07:47 PM
Could you post a sample book that can show the issue.
0