Disable close button (X) on a User Form

[garden_rael]

Hi

I've created a user form with VBA in Excel...

I've put a close button there to close both form and worksheet... but the close (x) button on the upper right side of the form in enable so if the user closes the form using that button... he goes to my main worksheet where all my information is located...

How can I prevent that?

Is there a code to disable the close button (x) from the upper side and leave only MY close button that I programmed?

 

[Willem!]

Hi,

Check thiz out

 

[Norie]

Well you can't disabled it if you mean grey it out without using the Windows API.

But you could use the userforms QueryCode event to stop it being effective.

In the userform module:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = True
    
    MsgBox "Please use the command button to close the form."
End Sub

 

[Christof]

Hi,

Check thiz out

brilliant, thank you!

 

[Akashwani]

Hi, try this in your UserForm code...

'''''''This is to hide the X
Private Const GWL_STYLE = (-16)
Private Const WS_SYSMENU = &H80000
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 Declare Function _
   DrawMenuBar Lib "User32" (ByVal hWnd As Long) As Long

Ak

 

[TAPS_MikeDion]

Hi,
I was also looking to do this and tried Ak's code, but it didn't work. Using Will's or Norie's suggestion (same thing) works just fine. All you need to do to save the user another step, is copy the code from your existing CLOSE/EXIT command button and put it in place of the MsgBox line of code. That way, you get the same response regardless of which button is clicked on.

Works great, thanks to you both.

-Mike

 

[mikerickson]

Another approach would be to redirect the corner X to your Exit routine.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        Call butExit_Click
    End If
End Sub

Private Sub butExit_Click()
    MsgBox "goodbye"
    Unload Me
End Sub

 

[Akashwani]

I must apologise, I inadvertently left out a piece of code

Option Explicit
Private Const GWL_STYLE = (-16)
Private Const WS_SYSMENU = &H80000
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 Declare Function _
   DrawMenuBar Lib "User32" (ByVal hWnd As Long) As Long

 Private Sub UserForm_Initialize()
 Dim xl_hwnd, lStyle
 xl_hwnd = FindWindow(vbNullString, Me.Caption)
 If xl_hwnd <> 0 Then
        lStyle = GetWindowLong(xl_hwnd, GWL_STYLE)
        lStyle = SetWindowLong(xl_hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU)
        DrawMenuBar xl_hwnd
 End If
End Sub

I am so sorry for this novice error.

I hope this works now.

Ak

 

[maghedrizk]

I must apologise, I inadvertently left out a piece of code

Option Explicit
Private Const GWL_STYLE = (-16)
Private Const WS_SYSMENU = &H80000
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 Declare Function _
   DrawMenuBar Lib "User32" (ByVal hWnd As Long) As Long

 Private Sub UserForm_Initialize()
 Dim xl_hwnd, lStyle
 xl_hwnd = FindWindow(vbNullString, Me.Caption)
 If xl_hwnd <> 0 Then
        lStyle = GetWindowLong(xl_hwnd, GWL_STYLE)
        lStyle = SetWindowLong(xl_hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU)
        DrawMenuBar xl_hwnd
 End If
End Sub

I am so sorry for this novice error.

I hope this works now.

Ak

Thanks a lot AKA..perfect