07 Wb opened w/ 03 and it Reopens after Close

Closed
Josh - 11 May 2012 à 08:23
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 9 Oct 2012 à 19:47
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


Related:

6 responses

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
11 May 2012 à 17:59
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 May 2012 à 17:23
what is this "CloseDownTime" ?
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 May 2012 à 18:29
There are numerous reference to this in your code
I did not spot any place where this is being set
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
26 May 2012 à 07:47
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
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
30 May 2012 à 03:14
I am confused over the three events that you have coded for . What is your intent here for each event?
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
ExcelJosh Posts 12 Registration date Wednesday 8 August 2012 Status Contributor Last seen 12 October 2012
8 Oct 2012 à 07:59
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
9 Oct 2012 à 19:47
Could you post a sample book that can show the issue.