Dissable Close button on Userform or Assign Code

**RobynC** · 09-19-2007, 08:02 AM

Hi,

Is there a way to disable the close icon on a userform? (The x in the top right hand corner)

Basically I need the sub to exit if the userform is canceled rather than if Ok is selected (and hence data has been entered). I have this working fine by setting a variable (IERROR ) to true if [cancel] is selected. however if the user were to close the userform wit the 'x' then this value would not be assigned and the program would continue to try and run without the vital data.

Ideally I would like to be able to assign the [Cancel] code to the 'x' as well, but failing that, dissabling it will also solve the problem. As a third option is there a way to tell whether the userform was exited this way rather than via a command button?

**dominicb** · 09-19-2007, 09:08 AM

Good afternoon RobynC

This code will stop the user from being able to close the form via the X control - just drop it into your userform module

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "Close via the button"
Cancel = True
End If
End Sub

If you prefer, I can supply you with a piece of code that will supress the X from being displayed - a userform without the close button. This trick utilises API's, so is a little trickier to implement (but looks good), but I'll dig it out for you if you (or anyone else) wants it. if you decide to use that, however, you'll still need the code supplied above to stop people crashing out of the form by pressing Ctrl + F4.

HTH

DominicB

**RobynC** · 09-19-2007, 09:17 AM

Hey,

Thank-you, that's great.

If you can find the code to stop the button being shown that would be great, I agree it would look very smart and the smarter my program looks, the more impressed the boss will be and that can only be a good thing.

Regards

Robyn

**royUK** · 09-19-2007, 09:25 AM

here's some code to hide the "X"

Place this code in a standard module,

Public Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Public Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Const GWL_STYLE = -16
Public Const WS_SYSMENU = &H80000

Then in the userform itself,

Private Sub UserForm_Initialize()
Dim hWnd As Long, lStyle As Long
If Val(Application.Version) >= 9 Then
hWnd = FindWindow("ThunderDFrame", Me.Caption)
Else
hWnd = FindWindow("ThunderXFrame", Me.Caption)
End If
lStyle = GetWindowLong(hWnd, GWL_STYLE)
SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)
End Sub

**dominicb** · 09-19-2007, 09:29 AM

Hi RobynC

If you can find the code to stop the button being shown that would be great

Thought you'd say that...

the more impressed the boss will be and that can only be a good thing

But any raise you've gotta split 50/50

Put this in a normal 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

Sub HideX(UForm As Object)
Dim Win As Long, WinStyle As Long
If Val(Application.Version) >= 9 Then
Win = FindWindow("ThunderDFrame", UForm.Caption)
Else
Win = FindWindow("ThunderXFrame", UForm.Caption)
End If
WinStyle = GetWindowLong(Win, -16)
SetWindowLong Win, -16, WinStyle And Not &H80000
End Sub

Then this in your userform ;

Private Sub UserForm_Initialize()
HideX UserForm1    'Change this to whatever your userform is called
End Sub

HTH

DominicB

**RobynC** · 09-19-2007, 10:07 AM

Ooh two ways of doing it, which are very similar. Sorry RoyUK but going to use Domonicb's version as it'll look neater in the userform's code, but thank-you both anyway.

In the spirit of me actually understanding what is going on, rather than just copy and pasting, is my following understanding correct?

This bit creates the functions that are used in the sub. Don't quite understand what hWnd, and nIndex are but assume they are items that define how the bar at the top of the userform looks. NewhWnd is used to change hWnd when it shows the 'x' to not showing the 'x'?

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

This bit checks which version of excel you are using. ThunderDFrame and ThunderXFrame refer to the same thing but its label has been different since version 9. Is the think it refers to the bar at the top of the userform?

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

So Win is the default windows coulouring of the bar and -16 means that it tops 16 points from the right hand side which is short of where the x is right?
The next bit then fills in the last section with colouring to match the windows fade rather than &H800000 which I assume is a kinda grey?

WinStyle = GetWindowLong(Win, -16)
SetWindowLong Win, -16, WinStyle And Not &H80000

Is that anywhere near correct??

As for sharing any bonuses, there are giong to be plenty more questions over the next couple of months I'm sure, so I'll consider that based on how helpful you are in the future!

Dissable Close button on Userform or Assign Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Dissable Close button on Userform or Assign Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions