Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UserForm Close (?) Event (2000)

    Is there any way to trap the userform close event? or before close event? This is what I have:

    <pre>Private Sub UserForm_Activate()
    ThisWorkbook.Sheets("Salesrecord").Unprotect password:=PW
    End Sub
    </pre>


    <pre>Private Sub UserForm_Deactivate()
    ThisWorkbook.Sheets("Salesrecord").Protect password:=PW
    End Sub
    </pre>


    The activation of the userform seems to trigger the code fine...when the userform comes up the sheet is unprotected so changes can be made to some linked textboxes. After the user changes the necessary information, he/she can close the form with the following "Close" button code:

    <pre>Private Sub CommandButton1_Click()
    ThisWorkbook.Sheets("Salesrecord").Protect password:=PW
    ProjSales.Hide
    End Sub
    </pre>


    The "Close" button reprotects the sheet fine. However, if the user clicks the "X" (close) button at the top right of the userform, then the deactivate event doesn't happen...the sheet is still unprotected after the form is hidden. How to I do something when the userform is closed?

    Any ideas or suggestions?

  2. #2
    New Lounger
    Join Date
    Nov 2001
    Location
    Amsterdam
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Close (?) Event (2000)

    Hello

    Try this event, it fires for me when you click the x

    Private Sub UserForm_Terminate()
    msgBox "terminate event", vbOKOnly
    End Sub

    Cheers

    Mark

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm Close (?) Event (2000)

    You can trap that situation with thw QueryClose event, which can be cancelled.<pre>Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    End If
    End Sub</pre>

    The above code will disable the x button and also Alt-F4 and the menu and the control menu (right click on the userform caption bar). If you do not wish to cancel the event you can place some other code instead of Cancel = True.

    See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=88475&Search= true&Forum=xl&Words=CloseMode&Match=And&Searchpage =0&Limit=25&Old=allposts&Main=88414
    >This Thread</A>

    Andrew C

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Close (?) Event (2000)

    Thanks Andrew, didn't know that one even existed. Any idea why it is called queryclose? Doesn't even seem related to the action.

    Thanks,
    Mike

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm Close (?) Event (2000)

    Mike

    I can only guess as to the nomenclature, and suspect that it is named because you can Query the close mode, i.e. the Unload command (vbFormCode = 1), Windows shutdown (vbAppWindows = 2), close via TaskManger (vbAppTaskManager = 3), or by the UserForm menu/caption bar (vbFormControlMenu = 0).

    I hope that explains it, but maybe somebody else has a better explanation.

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •