MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Two Easy Questions

Posted by Cory on June 26, 2001 6:44 AM

How do I assign code to the close button(x) on the top right corner of a userform?

Also, I was reading in the help section about how "every userform comes enabled with maximize, restore, and close buttons." Why is it that my userforms only come with a close button?

Any good answer is greatly appreciated...

Thanks in advance,

Posted by baffled on June 26, 2001 11:19 AM

how come the hardest questions are always posted by someone who thinks they're "easy"?

Posted by Dax on June 26, 2001 3:42 PM


These are indeed not easy questions. Well perhaps the first one is.

If you want to assign code so that the user can't close the userform using the cross button you use the QueryClose event i.e.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
MsgBox "The user closed the form by using the close button"
MsgBox "The user closed the form by using some other method"
End If
End Sub

The second question is more of a poser. The help guide does indeed say that userforms are "equipped" with minimise and maximise buttons but this doesn't seem to be the case. In fact, I only know how to provide these buttons using Windows API calls. Here is some example code: -

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 Const GWL_STYLE As Long = (-16)
Private Const WS_SYSMENU As Long = &H80000
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000

Private Sub UserForm_Activate()
Dim lFormHandle As Long, lStyle As Long
lFormHandle = FindWindow("ThunderDFrame", Me.Caption)
lStyle = GetWindowLong(lFormHandle, GWL_STYLE)
lStyle = lStyle Or WS_SYSMENU
lStyle = lStyle Or WS_MINIMIZEBOX
lStyle = lStyle Or WS_MAXIMIZEBOX
SetWindowLong lFormHandle, GWL_STYLE, (lStyle)
DrawMenuBar lFormHandle
End Sub

This will give your forms that max, min and close buttons. There are API calls to do loads more things such as give the userform a place on the taskbar, allow user resizing, allow modeless forms in Excel 97 and so on.