Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable "X" Box On UserForms (2000)

    I want to had absolute control over when the user closes my userform. Is there a way to remove the "X" in the upper right hand corner for userforms?

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

    Re: Disable "X" Box On UserForms (2000)

    You can disable the button so that clicking it has no effect with th efollowing code<pre>Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
    Cancel = True
    End If
    End Sub</pre>

    That code goes in the UserForm object, and will disable the x button from closing the form. However if you have a Cancel button it will operate as usual.

    Andrew C

  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: Disable "X" Box On UserForms (2000)

    Adam,

    That works fine except that it does not trap ALT-F4, which could be used to close the Form.

    However if you use

    Private Sub UserForm_Initialize()
    HideCloseButton Me
    Application.OnKey "%{F4}", ""
    End Sub

    that should be remedied.

    It would be necessary to reverse that when the form is finished with.

    Andrew

  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: Disable "X" Box On UserForms (2000)

    Application.OnKey "%{F4}", "" does not seem to disable the userform close event, although once you execute it you cannot close XL by using ALT-F4 - strange.

    Looks like the best solution is to combine both the QueryClose and API calls if the removal of the X is important.

    Andrew C

  5. #5
    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: Disable "X" Box On UserForms (2000)

    The following should work (compliments of a reply from Brooke a while back):

    Declarations section of the user form:
    <pre>Private Declare Function FindWindowA Lib "User32" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function GetWindowLongA Lib "User32" _
    (ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function SetWindowLongA Lib "User32" _
    (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    </pre>


    Then this in the userform activate event:

    <pre>Private Sub UserForm_Activate()
    Dim hWnd As Long, exLong As Long
    hWnd = FindWindowA(vbNullString, Me.Caption)
    exLong = GetWindowLongA(hWnd, -16)
    If exLong And &H880000 Then
    SetWindowLongA hWnd, -16, exLong And &HFF77FFFF
    Me.Hide: Me.Show
    End If
    End Sub
    </pre>


  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable "X" Box On UserForms (2000)

    Agreed - the API calls should be used in combination with overriding the Alt+F4 behaviour to guarantee complete control.

    Since OnKey is an application method, it's hardly suprising that it stops you from closing Excel using Alt+F4, as the call to this method obviously has Application level scope. What I DO find suprsising is that you can't 'cancel' the OnKey call. It seems once you've set the OnKey for Alt+F4 to "", you can't change back to the default behaviour again.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable "X" Box On UserForms (2000)

    very similar to the solution I provided yesterday, but read further down that thread as their is another issue with Alt+F4

  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: Disable "X" Box On UserForms (2000)

    Michael,

    I think ALT-F4 also gets to work and that one and closes it., and so does not give Kevin
    <hr>absolute control over when the user closes my userform<hr>
    It could be used as well as the QueryClose event so that the button is not alone disabled, but removed.

    Andrew

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

    Re: Disable &quot;X&quot; Box On UserForms (2000)

    Executing <pre> Application.OnKey "%{F4}"</pre>

    should restore the default.

    Andrew

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable &quot;X&quot; Box On UserForms (2000)

    you're right, I didn't read the help closely enough!

    that should go in the form's closing code then

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable 'X' Box On UserForms (2000)

    This code placed in the userform's module will hide the X button:-

    Option Explicit

    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
    Private Const WS_SYSMENU = &H80000
    Private Const GWL_STYLE = (-16)

    Private Sub UserForm_Initialize()
    HideCloseButton Me
    End Sub

    Public Sub HideCloseButton(ByVal Form As MSForms.UserForm)
    Dim hWnd As Long, lStyle As Long

    Select Case Int(Val(Application.Version))
    Case 8 'Excel 97
    hWnd = FindWindow("ThunderXFrame", Form.Caption)
    Case 9 'Excel 2000
    hWnd = FindWindow("ThunderDFrame", Form.Caption)
    End Select

    'Get the current window style
    lStyle = GetWindowLong(hWnd, GWL_STYLE)

    'Turn off the System Menu bit
    SetWindowLong hWnd, GWL_STYLE, lStyle And Not WS_SYSMENU

    End Sub


    (This originally came from Stephen Bullen's excel page http://www.bmsltd.ie/Excel/Default.htm )

    Edited Mar 13th 2004 to update link

Posting Permissions

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