Preventing user from closing form with "X"

[peter.thompson]

Am using the following code to make sure user closes a form correctly

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode A
Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You must use the form 'close' button", 64, "Erro
Message"
End If
End Sub

I have 60 userforms. Rather than apply code in each form, is there
way to universally apply this to all forms in the workbook??

Follow up question, is it possible to remove the Microsoft "X" at th
top right hand side of the UserForm??

Any help much appreciated


Peter (new to VBA...slowly getting there, thanks to all the good advic
from this forum!

 

[Nigel]

You can create a function in a general module that tests the value passed to
it as if they were in the form, the function returns a True or False
depending on the method used to close the Form. Try this......

In each userform change the Query Close event as follows

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Cancel = UnloadError(Cancel, CloseMode)
End Sub

In a general module put in this function ......

Function UnloadError(Cancel As Integer, CloseMode As Integer)
UnloadError = False
If CloseMode = vbFormControlMenu Then
UnloadError = True
MsgBox "You must use the form 'close' button", 64, "Error Message"
End If
End Function

If you wish to remove the X you have to remove the Form Control Bar, this
can be achieved using the following.....
This uses windows library calls it needs to be installed in each userform.

In the userform module declaration area (before any modules) ....

'heading for toolbar remover
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
' ******** end of code

In the userform initialize event place this code ........

Call HideCaptionBar
' ********end of code

In the userform put this function......

Private Function HideCaptionBar()
Dim lngHnd As Long
Dim lngStyle As Long
Dim lngH(1) As Long
Const GWL_STYLE = (-16)
Const WS_CAPTION = &HC00000
lngH(0) = Me.Height - Me.InsideHeight
If Val(Application.Version) > 8 Then
lngHnd = FindWindow("ThunderDFrame", Me.Caption)
Else
lngHnd = FindWindow("ThunderXFrame", Me.Caption)
End If
lngStyle = GetWindowLong(lngHnd, GWL_STYLE) And Not WS_CAPTION
SetWindowLong lngHnd, GWL_STYLE, lngStyle
DrawMenuBar lngHnd
lngH(1) = Me.Height - Me.InsideHeight
Me.Height = Me.Height + lngH(1) - lngH(0)
End Function
' ******* end of code



--
Cheers
Nigel



"peter.thompson"

 

[Remy]

There is no easy way to disable the close button in an Excel UserForm
to my knowledge. Your approach is the only one I know, besides using an
external dll for the form.
Don't really know about a universal way to apply that to every userform
either :-(.
What you could do is to use VBE:
http://www.cpearson.com/excel/vbe.htm
With that you could programatically alter all Userforms, but most
likely you spend more time figuring out how to do that than doing it
manually..

Cheers
Remy Blaettler
http://www.collaboral.com

 

[peter.thompson]

Thanks Nigel & Remy - much appreciated

Cheers

Pete