Dissable Close button on Userform or Assign Code

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?

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

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!