Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unclosable Forms (Excel 2000 SR1a)

    Dear all,

    I have a spreadsheet in which I wish to arrest almost all control away from the user (No Microsoft jokes please). I wish the user to control the spreadsheet by using controls or entering data on forms. My problem is that all of the forms I make have a close box, but if the user closes the form then they cannot do anything because their means of control i.e. the form is gone. I either want to prevent them from closing the form or initiate a macro upon them closing the form that reopens the form.

    Thanks

    Don <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Use the UserForm_QueryClose event for that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Similar questions have been discussed extensively in the Access forum. If you do a search there, you will find more information if you want it.

    You can control closing the form by using a boolean variable (CanClose in the example below) and a command button (CloseButton). Put something like the following code behind your forms:

    Private CanClose As Boolean

    Private Sub CloseButton_Click()
    CanClose = True
    Unload Me ' or Application.Quit or whatever
    End Sub

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = Not CanClose
    End Sub

    CanClose is False by default, so using the close box is canceled. When the user clicks the command button CloseButton, CanClose is set to true, so the close or quit action won't be canceled.

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

    Re: Unclosable Forms (Excel 2000 SR1a)

    If you include the following code in your forms, closing by clicking on the ' x ' in the top right corner, or using Alt-F4. The only way to close the form would be via code.<pre>
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
    Cancel = True
    End If
    End Sub</pre>

    Andrew C

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unclosable Forms (Excel 2000 SR1a)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Don

    In addition to all the wonderful replies the loungers have given you, there is an API call that would remove the three buttons on the Form's Title bar.

    Check the MSKB for examples.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Dear all,

    thanks for the replies so far. From my point of view the problem is solved.

    Thanks again for your help!

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  7. #7
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Thankyou for your reply,

    I'm lacking in knowledge on using events. The only events I've used in the past have been button presses and opening of workbooks. Hopefully, this will lead me onto bigger and better things.

    Don

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Thanks for your reply,

    yet another way to do the job. Thanks also for the tip on the Access forum, I should take some to look at it, as I have progressed a shorter distance down the teach myself access trail than the teach myself excel trail.

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  9. #9
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Thanks Andrew,

    it is certainly easy when you know how.

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  10. #10
    New Lounger
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unclosable Forms (Excel 2000 SR1a)

    Thanks Wassim,

    I'll check it out. Getting so many good replies overnight was certainly a bonus.

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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