Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent User Closing UserForm (XP/2k)

    I think I've seen this question before but I cannot trace it:

    Is there a way to prevent a user from closing a userform by clicking the close icon? I've looked through the form properties but nothing appears to relate to this question.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: Prevent User Closing UserForm (XP/2k)

    You can use the QueryClose event of the UserForm for this. The event procedure has two arguments:

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

    Cancel is False by default, but you can set it to True to cancel closing the form.

    CloseMode provides information about how the form is being closed:

    vbFormControlMenu = 0 = user clicked close button in upper right, or system menu in upper left.
    vbFormCode = 1 = form is being closed from code.
    vbAppWindows = 2 = Windows is being closed down.
    vbAppTaskManager = 3 = Excel is being closed by Task Manager.
    (vbFormMDIForm = 4 doesn't apply to UserForms)

    So you could have code like this:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Select Case CloseMode
    Case vbFormControlMenu
    Cancel = True
    Case vbFormCode
    If MsgBox("Are you sure?", vbQuestion + vbYesNo) = vbNo Then
    Cancel = True
    End If
    End Select
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    Hans,
    The White Knight of Holland rescues another dummy in distress.
    Thanks for all your help. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    This is right from VB Help. It is the example from the QueryClose Event in XL97.

    Steve

    <blockquote><hr>The following code forces the user to click the UserForm

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    Thanks, Steve.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    Another way would be to not show the close [x] on the Form in the first place.

    Do you want me to post the code?


    zeddy

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    Hi Zeddy - Yes please!
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    To hide the little X that appears at the top right of a userform, you can use the following code that you would put in the userform's module:

    'this goes at the top of the module:

    Private Declare Function FindWindow Lib "user32" _
    Alias "FindWindowA" (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    Private Declare Function GetWindowLong Lib "user32" _
    Alias "GetWindowLongA" (ByVal hWnd As Long, _
    ByVal nIndex As Long) As Long

    Private Declare Function SetWindowLong Lib "user32" _
    Alias "SetWindowLongA" (ByVal hWnd As Long, _
    ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Const GWL_STYLE = (-16)
    Const WS_SYSMENU = &H80000

    Private Sub UserForm_Initialize()
    'this hides the X on the caption line
    Dim hWnd As Long, a As Long

    hWnd = FindWindow("ThunderXFrame", Me.Caption)
    a = GetWindowLong(hWnd, GWL_STYLE)
    SetWindowLong hWnd, GWL_STYLE, a And Not WS_SYSMENU
    End Sub



    I need to check the FindWindow("ThunderXFrame" bit as I remember this depends on the Excel version you are running. I'm not at home so I'll do another post later.

    zeddy

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent User Closing UserForm (XP/2k)

    Hi Rob,

    I found this in a book somewhere when I first started programming in Excel. It's always worked fine for me as I have this control issue about letting the user get behind the userform!!

    Private Sub UserForm_QueryClose _
    (Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    MsgBox "You must click the EXIT button to close the program"
    Cancel = True
    End If
    End Sub

    I then just put a command button on the form labeled exit that they use to close the program.

    Just a shorter alternative.

    Leesha

  10. The Following User Says Thank You to Leesha For This Useful Post:

    motivated (2013-03-02)

  11. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent User Closing UserForm (XP/2k)

    Awesome Zeddy ... I have been using the QueryClose event to trap the Close button, but this is even better. No Close button.

    ThunderDFrame is for version 9 or better, else use ThunderXFrame. (found this on the web)

    <pre>Private Sub UserForm_Initialize()
    Dim hWnd As Long
    Dim a As Long

    If Val(Application.Version) >= 9 Then
    hWnd = FindWindow("ThunderDFrame", Me.Caption)
    Else
    hWnd = FindWindow("ThunderXFrame", Me.Caption)
    End If

    a = GetWindowLong(hWnd, GWL_STYLE)
    SetWindowLong hWnd, GWL_STYLE, a And Not WS_SYSMENU
    End Sub

    </pre>

    edited to correct inaccurate information caused by a disease that I suffer from - RCI

Posting Permissions

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