I am Having a challenge with ThisWorkbook.Close

[Mister_joe]

Hi All,
I am wondering whether the following command is triggering other events in my code:


ThisWorkbook.Close Savechanges:=False


I have a macro enabled Workbook on which I wanted to place a splash screen. The splash screen was intended to display for about five seconds and then disappear. However, if the user is not willing to wait for the splash screen to disappear, he or she could just click on a command buton on the form. If the Workbook is the only one in the Workbooks Collection at the time the user clicks the button, the application should close. If there are other Workbooks in the Workbooks Collection when the user clicks the button, only the ActiveWorkbook (the one that holds the splash screen) should close. Well, my code works this way, except that when I have multiple Excel files open and I close the one that has the splash screen, I get Excel security notice as shown in the image. When I click on Enable Macros, my code runs all over again. This is not what I want. I get the impression that when the Workbook closes, it triggers the UserForm_Initialize event. Could you be kindly run the code shown below and see if you can reproduce the security notice. Any idea how to work around this?
EnaleMacro.jpg

'Splash screen event handler (the form was named frmSplashScreen)
Private Sub UserForm_Initialize()
      Application.OnTime Now + TimeValue("00:00:05"), "KillTheSplashScreenForm"
End Sub


'Command button on the splash screen (the button was named cmdSplashScreenQuit)
Private Sub cmdSplashScreenQuit_Click()
    'When the user chooses to quit the programme, the workbook should be closed           
    'If there are no other Excel files open, then just close the application
    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
       ThisWorkbook.Close Savechanges:=False
    End If   
End Sub


'This procedure is on the General module
Private Sub KillTheSplashScreenForm()
     'Kill the splash screen form
    Unload frmSplashScreen          
End Sub


'Workbook event handler
Private Sub Workbook_Open()   
    frmSplashScreen.Show
End Sub

[snb]

You have to disable the ontime procedure (dive into the VBEditor helpfiles)

[GTO]

As snb stated, you'll want to re-read the help topic on the OnTime Method. That said, to have the Schedule arg cancel, you'll need to save the EarliestTime in a variable.

I have a question though. I could not get the picture big enough to see clearly (but then again, I am fairly bleary-eyed at the moment), but presuming the form's title bar, or more specifically the Close button is still showing/enabled (and the form's menu), then what happens if the user just clicks the "x" at the upper right-hand corner of the form?

NOT tested at all, but maybe put the (modified) code under QueryClose event. If NOW() is less than the EarliestTime variable's value, then cancel OnTime and the other stuff.

Again, not tested, just a thought.

Mark

[Mister_joe]

Thanks, but if I disable to OnTime procedure, how would the splash screen disappear after the preset time?

[GTO]

You would only "disable" (cancel) the OnTime if the user kills the form before OnTime's procedure has run. Does that make sense?

[Mister_joe]

The image has been attached. You may wish to open it with Paint. Your suggestions do make sense, but I am still having problems with the OnTime procedure. If I issue

Application.OnTime EarliestTime:=Now, Procedure:="KillTheSplashScreenForm", LatestTime:=Now, Schedule:=False 
ThisWorkbook.Close

I get the following error message:Run-time error '1004'
Method 'OnTime' of object '_Application' failed

If I issue

ThisWorkbook.Close
Application.OnTime EarliestTime:=Now, Procedure:="KillTheSplashScreenForm", LatestTime:=Now, Schedule:=False"

There is no effect. The OnTime procedure is not cancelled.

[Bob Phillips]

As Mark said, you set the Ontime runtime into a variable and pass that to the initial run, then you use that variable when cancelling the OnTime. You can't use Now because the Now when the OnTime was initially issued will be different to the Now when it is cancelled, hence they do not match up.

[Mister_joe]

Thanks. Here is what I have now. It seems to be alright.

Dim maxWait As Variant
Dim minWait As Variant

Private Sub cmdSplashScreenQuit_Click()
    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
    
            'since minWait = maxWait, the procedure will not run even if schedule = True.
            Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False
            
            ThisWorkbook.Close Savechanges:=False    
            
    End If     
    End Sub


Private Sub UserForm_Initialize()
    minWait = Now() + TimeValue("00:00:05") 'do not execute the procedure before this time
    maxWait = Now() + TimeValue("00:00:05")  'do not execute the procedure after this time

    Application.OnTime Earliesttime:=minWait, Procedure:="KillTheSplashScreenForm", Schedule:=True  
End Sub

[SamT]

Dim maxWait As Variant 
Dim minWait As Variant 
 
Private Sub cmdSplashScreenQuit_Click() 
KillFormNow
End Sub 

Private Sub UserForm_click()
KillFormNow
EndSub
 
 
Private Sub UserForm_Initialize() 
    minWait = Now() + TimeValue("00:00:05") 'do not execute the procedure before this time
    maxWait = Now() + TimeValue("00:00:05") 'do not execute the procedure after this time
     
    Application.OnTime Earliesttime:=minWait, Procedure:="KillTheSplashScreenForm", Schedule:=True 
End Sub 

Private Sub KillFormNow()
If Application.Workbooks.Count = 1 Then 
Application.Quit
Else
'since minWait = maxWait, the procedure will not run even if schedule = True.
Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False
ThisWorkbook.Close Savechanges:=False
End If
End Sub

Private Sub KillTheSplashScreenForm()
Unload frmSplashScreen
End Sub

[Mister_joe]

Thanks SamT. Your's is even better.

[SamT]

If you set cmdSplashScreenQuit's Cancel and Default properties both to True, KillFormNow will run when the Space-bar, or the Enter Key, or the Escape Key is pressed.

For guaranteed results, add the line "cmdSplashScreenQuit.SetFocus" to the Initialize sub.

For a better looking Splash Screen, try setting the CommandButton's BackStyle Property to Transparent.

When you are totally satisfied with it, will you open a new workbook, and in the VBE Project Explorer, Drag the Form module and the Module with Sub KillTheSplashScreenForm() to the new workbook's Project? Then add the Workbook_Open code to the new book.

After insuring that the new book runs the Splash screen properly, upload it here as an example for others.

[GTO]

@SamT:

Greetings,

Maybe I am missing something, but what happens if you kill the form via the 'X' button in the upper-right, or ALT+SPACEBAR;C prior to OnTime kicking into gear? At least for me, the OnTime seems not cancelled, and UnLoad (in 2010) re-initializes the form (and restarts the OnTime).

Mark

[SamT]

UnLoad (in 2010) re-initializes the form (and restarts the OnTime).

Wow! That's unexpected. I would think that all methods of unloading the form should give the same results. From what you're telling me the Reset OnTIme code should be in the Form_Terminate event procedure. Then rewrite the general module code to

Do Events
If Not Form is Nothing Then Form.KillMeNow

In 2003, the unload sub just runs, but nothing else happens.

The OP seems happy and he's running it in an xlsm file...

[snb]

In the userform1 codemodule:

Private Sub UserForm_Initialize()
   c00 = DateAdd("s", 5, Now)
   Application.OnTime c00, "frmstop"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    frmstop
End Sub

In a macromodule:

Public c00
Public Sub frmstop()
   If DateDiff("s", c00, Now) < 5 Then Application.OnTime c00, "frmstop", False
   Unload UserForm1
End Sub

[GTO]

Hi Sam,

Wow! That's unexpected. I would think that all methods of unloading the form should give the same results. From what you're telling me the Reset OnTIme code should be in the Form_Terminate event procedure.

Probably overly cautious, but I personally would think of placing the initial .OnTime somewhere other than the form's initialize event. Regardless, I do think that using QueryClose or Terminate would be better for cancelling the .OnTime, as this covers any manner of the form unloading. The problem I spotted is that in running into a Unload MyForm, where MyForm is already unloaded, this causes the form to first initialize, then unload. Hence, the .OnTime gets started again...

This happens any time that the form, while not loaded, is referred to. Thus...

Then rewrite the general module code to

Do Events
If Not Form is Nothing Then Form.KillMeNow

...will always pass the If test. Either the form is loaded, or, if the form is not loaded, it gets auto-instanced by being referred to in the If test.

In 2003, the unload sub just runs, but nothing else happens.

The OP seems happy and he's running it in an xlsm file...

If I understood your post at all, everything in the first block of code (at #9) is in the form and KillTheSpashScreenForm is in a standard module, right? I blew the dust off of my fried-keyboard laptop w/XP and Excel 2000; same results.

Try stepping thru this in a new workbook:

UserForm1 Code:

Option Explicit
  
Private Sub UserForm_Initialize()
  Stop
End Sub

Standard Module:

Option Explicit
  
Sub example()
  
  If Not UserForm1 Is Nothing Then  'Always passes test
    
    Unload UserForm1 'unloads the form
    
    MsgBox UserForm1 Is Nothing 're-auto-initializes the form
    
    Unload UserForm1 'Unloads the form
    
    Unload UserForm1 're-auto-initializes the form, then unloads
    
  End If
  
End Sub

Hopefully I expressed that a bit better?

Mark

PS - you might enjoy reading http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

[SamT]

GTO, as usual, you are right. Thank you for the lesson and the link. It is always good to learn more bout my favorite subject.

In my "test" in #13, I obviously used way too simple of an experiment and the form was merely loading and unloading without my notice. My bad

This explains why in #14, snb used a public variable in the MacroModule, his sub frmstop doesn't have to refer to the form to see if it is still open.

The research and rereading all the code herein made me realize that the sub KillFormNow in my #9 must actually read:

 Private Sub KillFormNow() 

             'since minWait = maxWait, the procedure will not run even if schedule = True.
            Application.OnTime EarliestTime:=minWait,  Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait,  Schedule:=False 

        If Application.Workbooks.Count = 1 Then 
            Application.Quit 
        Else 
            ThisWorkbook.Close Savechanges:=False 
        End If 
    End Sub

What do you think about the OPs comment in re minWait = maxWait?

Again, I thank you for your tutoring time.

[GTO]

GTO, as usual, you are right. Thank you for the lesson and the link. It is always good to learn more bout my favorite subject.

Hi Sam,

My pleasure and of course you are most welcome. I just wish I had your "brain" for relationships and DB design. I would so much like to be able to combine some stuff that I "know" can go together. I know that learning is what makes it interesting and challenging, but some days, I sure would like to be able to just "download" others knowledge. (I realize that life would become quite the bore if that were possible, just saying...)


Well, here we go...

In my "test" in #13, I obviously used way too simple of an experiment and the form was merely loading and unloading without my notice. My bad

This explains why in #14, snb used a public variable in the MacroModule, his sub frmstop doesn't have to refer to the form to see if it is still open.

The research and rereading all the code herein made me realize that the sub KillFormNow in my #9 must actually read:

 Private Sub KillFormNow() 

             'since minWait = maxWait, the procedure will not run even if schedule = True.
            Application.OnTime EarliestTime:=minWait,  Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait,  Schedule:=False 

        If Application.Workbooks.Count = 1 Then 
            Application.Quit 
        Else 
            ThisWorkbook.Close Savechanges:=False 
        End If 
    End Sub

@snb:

No offense intended, but even a blond guy can paste that little bit of code in a workbook accurately and run it. I am awfully confident I did so accurately, and when run, [bleep..........]!

@SamT:


Errr... well... about snb's suggestion (at all: please take this in the light-hearted manner intended) , did you try it? Before you do, close anything else you are working on.

See, yours would continue to call Unload, which in turn (as the form was already unloaded), would re-initialize the form - then re-unload it if-you-will, and during initialize, set another .OnTime. snb's does similarly, just way worse. As snb has called frmstop in QueryClose, if we 'manually' kill the form (the 'X' close button) before .OnTime calls frmstop, then we have already unloaded the form. Now when QueryClose calls frmstop, the UnLoad is run into again, and re-initializes, setting another new .OnTime, before again unloading. Same 'recurse'.

If we let the form run, then the .OnTime calls frmstop before the first QueryClose, so we just started our "evil loop" from a different place.

I have attached a modified version of snb's suggested code to sort of show what is happening, but it is not really spot-on, as collecting the values to variables and writing them to a textfile takes time, so some of the processing appears lost from snb's literal suggestion. If you have everything else closed, you can run snb's code from the vbe windows and watch. (Seriously, don't have anything else open)

In the form's initialize, hit F5. Once the userform disappears, you will be staring at vbe. If the form is displayed, close that window quickly, so that you are staring at:

Private Sub UserForm_Initialize()
   c00 = DateAdd("s", 5, Now)
   Application.OnTime c00, "frmstop"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    frmstop
End Sub

Keep your mouse pointer left of

Private Sub UserForm_Initialize()

so that you can click and put a break in. Now watch the titlebar. You will see that code gets run every few seconds, and as far as I can tell, starts adding OnTime's as it starts running more of the time. If you leave it go, pretty soon Excel is unresponsive...

As mentioned, I included a modified copy, to sort of see what happens. Again though, not exactly accurate due to the slowing of overall execution. It does however include a constant that limits how many times we'll 'recurse'.

To try this:

F5 from A_RunIt.

When the form disappears, just wait until the Stop in UserForm_Initialize is reached, Now F8 until all done (the second time the End Sub is reached in frmstop).

Now, F8 or F5 thru Cleanup to close the created text file and reset everything. You can read thru the created text file.

Okay - as to your code, I think KillFormNow is fixed, but we did not handle the user closing the form "manually". In summary, I think we are facing a "bad" combination of three things.

1. We start the .OnTime in the form's initialize, which means accidently starting a new .OnTime if we accidently initialize.
2. We refer to the form by its CodeName, rather than setting a variable to a new instance of the form.
3. We aren't handling if the user closes the form "manually".

Here (included in the zip) is your last, modified a tiny bit. It seems to work for me.

[Option Explicit
  
'***Post 9
Dim maxWait As Variant
Dim minWait As Variant
  
Private Sub cmdSplashScreenQuit_Click()
  'KillFormNow
  Unload Me
End Sub
  
Private Sub UserForm_click()
  'KillFormNow
  Unload Me
End Sub
     
Private Sub UserForm_Initialize()
  
  minWait = Now() + TimeValue("00:00:05") 'do not execute the procedure before this time
  maxWait = Now() + TimeValue("00:00:05") 'do not execute the procedure after this time
  
  Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", Schedule:=True
  
End Sub
'***End Post 9
  
'***Post 16
Private Sub KillFormNow()
  
  If Now < minWait Then 'added _mws
    
    'since minWait = maxWait, the procedure will not run even if schedule = True.
    Application.OnTime EarliestTime:=minWait, Procedure:="KillTheSplashScreenForm", LatestTime:=maxWait, Schedule:=False
    
    If Application.Workbooks.Count = 1 Then
      'Application.Quit
      ThisWorkbook.Close SaveChanges:=False
    Else
      ThisWorkbook.Close SaveChanges:=False
    End If
    
  End If
  
End Sub
  
Private Sub UserForm_Terminate()
  KillFormNow
End Sub

In gist, have Terminate call KillFormNow, and in that procedure, test to see if the user dismissed the form before the .OnTime's call ran.

FWIW, here is what I came up with (also attached):

In a Standard Module:

Option Explicit
  
Private MyForm      As frmOnTimeTest
Public timeEarliest As Date
  
Public Sub ButtonClick01()
  
  '//Less problems if we use a variable to reference an instance of the form.               //
  Set MyForm = New frmOnTimeTest
  
  If IsLoaded(MyForm) Then
    
    timeEarliest = Now + TimeSerial(0, 0, 5)
    '// Might well be better ways, but to ensure specific instance of the form is loaded... //
    Application.OnTime EarliestTime:=timeEarliest, _
                       Procedure:=ThisWorkbook.Name & "!'basOnTimeTest.KillSplash GetForm'"
    MyForm.Show
    
  End If
  
End Sub
  
Private Sub KillSplash(frm As Object)
  
  '// As 'Is Nothing' doesn't seem reliable, test some other way to see if the form is loaded.//
  If IsLoaded(frm) Then
    Unload frm
    'Debug.Print "Unloaded by .OnTime"
  End If
  
End Sub
  
'// I would imagine this to be similar to a read-only property get?  Anyways, it is     //
'// what I managed, in order to be able to pass an arg w/the .OnTime                    //
Public Function GetForm() As Object
  Set GetForm = MyForm
End Function
  
Private Function IsLoaded(ByVal frm As Object) As Boolean
Dim sTemp As String
  
  On Error Resume Next
  '// I have seen examples looping through the userforms collection to test against the //
  '// form's name, but I was thinking maybe producing an error by testing against a     //
  '// built-in property. An error should indicate that either no reference has been set,//
  '// or the reference has been but unloaded (i.e. Callee...has disappeared).           //
  sTemp = frm.Name
  IsLoaded = Err.Number = 0
  On Error GoTo 0
  
End Function

In a userform named frmOnTimeTest:

[Option Explicit
  
Private Sub cmdUnload_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Initialize()
  '// Just to see if stepping thru  //
End Sub
  
Private Sub UserForm_Terminate()
  
  '// Ensure the .Ontime hasn't already expired before cancelling //
  If timeEarliest > Now Then
    
    Application.OnTime EarliestTime:=timeEarliest, _
                       Procedure:=ThisWorkbook.Name & "!'basOnTimeTest.KillSplash GetForm'", _
                       Schedule:=False
    
    'Debug.Print ".Ontime cancelled, user dismissed form"
    
  End If
  
End Sub

What do you think about the OPs comment in re minWait = maxWait?

It seems neat, but I have not tested yet.

Way past when I should have hit the rack, a great day to you :-)

Mark

[GTO]

ACK! I forgot, read through: http://www.mrexcel.com/forum/excel-q...rocedures.html

[SamT]

See, yours would continue to call Unload, which in turn (as the form was already unloaded), would re-initialize the form - then re-unload it if-you-will, and during initialize, set another .OnTime.

But... It can't be already unloaded if the User can click on the form. If the User does click, the OnTIme is canceled. The only exception I can see would be if the User clicks at 4.999... seconds.


This version (#9+#13) works fine on XL2002

Dim maxWait As Variant
Dim minWait As Variant
 
Private Sub cmdSplashScreenQuit_Click() 'Form is visible
    KillFormNow
End Sub
 
Private Sub UserForm_click() 'Form is visible
    KillFormNow
End Sub
     
Private Sub UserForm_Initialize()
    minWait = Now() + TimeValue("00:00:05")
    maxWait = minWait
     
    Application.OnTime Earliesttime:=minWait, LatestTime:=maxWait, _
    Procedure:="KillTheSplashScreenForm", Schedule:=True
End Sub

Private Sub KillFormNow()
    Application.OnTime Earliesttime:=minWait, LatestTime:=maxWait, _
    Procedure:="KillTheSplashScreenForm", Schedule:=False 'Cancel OnTime
     
    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
        ThisWorkbook.Close Savechanges:=False
    End If
End Sub

Private Sub KillTheSplashScreenForm()
    Unload frmSplashScreen 'form is no longer visible
End Sub

[GTO]

But... It can't be already unloaded if the User can click on the form. If the User does click, the OnTIme is canceled...

... about snb's suggestion...

...See, yours would continue...

Okay - as to your code, I think KillFormNow is fixed, but we did not handle the user closing the form "manually"...

I am sorry Sam, I thought I was wording that okay, but was less than clear. Where you quoted from, what I was trying, albeit poorly, to communicate, was more at what yours would previously do (as in, your original suggestion). I was trying to compare this to where snb's suggestion faced similar issues (you had mentioned his suggestion).

Reference your current code, I very much agree that if the user clicks either the form or the command button, then the OnTime gets cancelled; I did say this later in the post. When I commented as to your updated code, what I meant by '...closing "manually"...' was if the user dismissed the form through the form's menu or close ('X') button.

I hope I've been at least a bit more articulate?

BTW, 'cat-killing' curiosity striking, did you try the workbooks I attached?

Mark