Pressing Close Button on Form stops macro 
Author Message
 Pressing Close Button on Form stops macro

Is it possible to stop a macro when a user presses the close button
(the x in the upper right of the form). I know about the
Userform_QueryClose procedure, but how do I tie the close event of the
form into shutting down the whole macro?


Thu, 14 Aug 2003 03:35:55 GMT  
 Pressing Close Button on Form stops macro
Hi Patrick,

Quote:
> Is it possible to stop a macro when a user presses the close button
> (the x in the upper right of the form). I know about the
> Userform_QueryClose procedure, but how do I tie the close event of the
> form into shutting down the whole macro?

What you need to do is declare a module-level boolean variable (StopMacro in
the example below) and set it to true in the QueryClose event procedure. The
code in your macro then tests the value of this variable, and if the value
is True, it exits the macro. The important thing is to add DoEvents
statements to your macro - these allow your macro to yield control to the
QueryClose event procedure so that the variable can be set. Here is a simple
example with a UserForm that has only 1 CommandButton. Try it and re-post to
the newsgroup if you still can't get yours to work.

----------------------------------------------------------

Option Explicit

Private StopMacro As Boolean

Private Sub CommandButton1_Click()

    Dim i As Long

    For i = 1 To 100000

        Debug.Print i

        DoEvents

        If StopMacro Then
            MsgBox "Macro terminated"
            Exit Sub
        End If

    Next i

    MsgBox "Macro completed"

End Sub

Private Sub UserForm_QueryClose _
    (Cancel As Integer, CloseMode As Integer)

    If Cancel = vbFormControlMenu Then
        StopMacro = True
    End If

End Sub

----------------------------------------------------------

Hope this helps.
ibby

Please post replies or follow-ups to the **newsgroup** so that participants
may benefit or contribute.



Thu, 14 Aug 2003 10:39:34 GMT  
 Pressing Close Button on Form stops macro
Thanks for the help. Is there some command in VBA that will abort a
macro withouth resorting to Exit Sub or something. Sort of like
Application.Quit will end Word.
Quote:

>Hi Patrick,

>> Is it possible to stop a macro when a user presses the close button
>> (the x in the upper right of the form). I know about the
>> Userform_QueryClose procedure, but how do I tie the close event of the
>> form into shutting down the whole macro?

>What you need to do is declare a module-level boolean variable (StopMacro in
>the example below) and set it to true in the QueryClose event procedure. The
>code in your macro then tests the value of this variable, and if the value
>is True, it exits the macro. The important thing is to add DoEvents
>statements to your macro - these allow your macro to yield control to the
>QueryClose event procedure so that the variable can be set. Here is a simple
>example with a UserForm that has only 1 CommandButton. Try it and re-post to
>the newsgroup if you still can't get yours to work.

>----------------------------------------------------------

>Option Explicit

>Private StopMacro As Boolean

>Private Sub CommandButton1_Click()

>    Dim i As Long

>    For i = 1 To 100000

>        Debug.Print i

>        DoEvents

>        If StopMacro Then
>            MsgBox "Macro terminated"
>            Exit Sub
>        End If

>    Next i

>    MsgBox "Macro completed"

>End Sub

>Private Sub UserForm_QueryClose _
>    (Cancel As Integer, CloseMode As Integer)

>    If Cancel = vbFormControlMenu Then
>        StopMacro = True
>    End If

>End Sub

>----------------------------------------------------------

>Hope this helps.
>ibby

>Please post replies or follow-ups to the **newsgroup** so that participants
>may benefit or contribute.



Fri, 15 Aug 2003 06:29:39 GMT  
 Pressing Close Button on Form stops macro

Quote:
> Thanks for the help. Is there some command in VBA that will abort a
> macro withouth resorting to Exit Sub or something. Sort of like
> Application.Quit will end Word.

Not quite sure what you mean, but the "End" statement terminates execution
of your code. However, this is not to be recommended. Instead, you should
handle the situation where the user terminates the macro and clean up
appropriately. Here is an outline:

-----------------------------------------------------

Dim i As Long

For i = 1 To 100000

    Debug.Print i

    DoEvents

    If StopMacro Then GoTo MacroTerminated

Next i

MsgBox "Macro completed"

Exit Sub

MacroTerminated:

    'Cleanup code here
    MsgBox "Macro has been Terminated"

End Sub

-----------------------------------------------------

Hope this helps.
ibby

Please post replies or follow-ups to the **newsgroup** so that participants
may benefit or contribute.



Fri, 15 Aug 2003 10:55:56 GMT  
 Pressing Close Button on Form stops macro
Hi All !

Crying out for help again...

Word 97.
I have created a list box in a userform, that I want to call from a
procedure in VBA module e.g.

public result
--------------
sub procedure()

Load SelectJob
  SelectJob.Show

    ThiIs = result

end sub
======================

I need to return the item chosen into the variable "result" (or any
variable).   The user form displays & I can get the "value" but cannot
figure out how to pass it back to the calling procedure for onward
processing....

I have searched the various fragments in Word help, and a sybex book on
word 97 VBA both have fragments on user dialog/listboxes etc, BUT do not
give a clue as to how to return the value.

Thanks in advance for any pointers

regards

John Doyle



Mon, 18 Aug 2003 08:39:46 GMT  
 Pressing Close Button on Form stops macro
Hi John,

Simplest thing is to declare a Public variable in a module. Within the
form's code, load up the variable with the value of the Listbox, and read
the variable once you close the form.

--
Regards
Jonathan West - Word MVP
MultiLinker - Automated generation of hyperlinks in Word
Conversion to PDF & HTML
http://www.multilinker.com
Word FAQs at http://www.multilinker.com/wordfaq
Please post any follow-up in the newsgroup. I do not reply to Word questions
by email


Quote:
> Hi All !

> Crying out for help again...

> Word 97.
> I have created a list box in a userform, that I want to call from a
> procedure in VBA module e.g.

> public result
> --------------
> sub procedure()

> Load SelectJob
>   SelectJob.Show

>     ThiIs = result

> end sub
> ======================

> I need to return the item chosen into the variable "result" (or any
> variable).   The user form displays & I can get the "value" but cannot
> figure out how to pass it back to the calling procedure for onward
> processing....

> I have searched the various fragments in Word help, and a sybex book on
> word 97 VBA both have fragments on user dialog/listboxes etc, BUT do not
> give a clue as to how to return the value.

> Thanks in advance for any pointers

> regards

> John Doyle



Mon, 18 Aug 2003 09:03:58 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. How to close a form by pressing a button

2. validate a textbox when windows is closed by pressing close button [X]

3. Form Close Button Does Not Close the Form

4. VB hangs when pressing the Stop button

5. Close IE after pressing a button....

6. Prevent user from closing form using caption close button

7. Form does not close from the UpperRight X Close button

8. problems using close [x] button when closing forms.

9. How to prevent user closing a mdi form using the close control button

10. Do not close form on clicking close button in Visual Basic

11. Capturing If user pressed max, min or normalize button on form

12. Event stop working when I close my form

 

 
Powered by phpBB® Forum Software