Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Intercept Form Close Event

    Userforms open as Modal only in Excel '97. I want to 'force' my users to use a minimize button i've placed on my form, so that they do not confuse the code. Any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    here we go again! <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    what do you mean by minimise?

    Brooke

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    min
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    So what you're after here is code that checks to see if the userform is open and if that is the case, intercepts the application.quit call from the little x, and goes down the application.minimise road instead? Or am I still in a different ball-park?

    Brooke

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    Sure, that'll work...

    the problem is that I can't retain the issue number if the form is closed outright.. I have to store that value in the sheets somewhere so that when they 'maximize' it retrieves the data they were last looking at.. [img]/forums/images/smilies/smile.gif[/img]

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Intercept Form Close Event

    Drk, what you need to do is trap the UserForm_QueryClose event and cancel it. Enter the following code by double clicking the form in VBA and pasting:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    End If
    End Sub

    Is that what you want ? How come your code is confusable ?

    Andrew

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    Because there is so much of it!! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Seriously, 40+ pages for an excel app is a bit much, don't you think? <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    The code is confuseable because there is a splash screen event which only displays the first time the form is launched each day. This required a special statement, which is nullified if the form is closed by itself. The splash screen is also retaining data on the username of the user, according to thier network login ID, leading to further confusion if the form is 'hard closed.' I've got get value events that determine if the code has been saved or not... etc.

    This monster is a compilation of every post i've put in this forum.. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> now I'm doing more to it... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Intercept Form Close Event

    40+ pages !!! - I wonder if Excel itself required much more coding.

    However, have you sorted the little "x" problem ?

    Andrew

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    Yep, it's dead alright!

    Now I just need to add my button so I wont have to CTRL+BREAK to get out of the form.. That might help... maybe... <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Intercept Form Close Event

    Do you mean add a button to close the form ?. How will that be different from clicking on the little x ?. Will it not re-introduce the problems ?

    Just wondering

    Andrew

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Form Close Event

    Nah.. here's what i'm doing to close the form:

    Sub btnmini_click()
    On Error GoTo ErrHandler
    Dim r As Integer
    r = 2
    Range("AD" & 2).Value = r
    Range("AD" & 1).Value = counter.Value
    userform.Hide
    Application.WindowState = xlMinimized
    Xit:
    Exit Sub
    ErrHandler:
    MsgBox ("Code Failure, contact your Technical Support Group.")
    Resume Xit
    End Sub

    The value "r" tells the code if the form has been minimized or not, and if so, calls the issue value from 'counter.value'.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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