minimizing a form *RESOLVED*

[marinus]

In vba for excel97 I designed a form, but when I open the form I can't minimize it. Does someone know if there's code so the form can be minimized?

[WorkHorse]

VBA extends the capabilities of an existing application. The UserForm is designed to be a custom dialogue box. It minimizes with the application. Like any other dialogue box, it pops up, allows the user to enter data or make some choices, then closes and returns functionality to the application.

Minimizing a UserForm can cause problems because users don't generally expect dialogue boxes to minimize and the minimized forms can hide without the user remembering that they are there. A better solution might be to pass all data collected from the UserForm to global variables so that the form can retrieve, display, and update the variables as the user opens and closes the UserForm.

If you are hell-bent on minimizing the UserForm, you can do so through API. Make sure that you set the UserForm property ShowModal to False, otherwise the user will not be able to do anything in Excel until the UserForm is closed.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) 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 GetWindowLong Lib "user32" Alias "GetWindowLongA" _
    (ByVal hWnd As Long, ByVal nIndex As Long) As Long
 
Private Const WS_MINIMIZEBOX = &H20000
Private Const GWL_STYLE = (-16)
 
Private Sub AddMinBox()
 
    ' Add minimize button to UserForm.
 
    Dim hWnd As Long
 
    Select Case Int(Val(Application.Version))
        Case 8      ' Office 97.
            hWnd = FindWindow("ThunderXFrame", vbNullString)
        Case 9, 10  ' Office 2000, XP
            hWnd = FindWindow("ThunderDFrame", vbNullString)
    End Select
    
    SetWindowLong hWnd, GWL_STYLE, GetWindowLong(hWnd, GWL_STYLE) Or WS_MINIMIZEBOX
    
End Sub
 
Private Sub UserForm_Initialize()
    AddMinBox
End Sub

[marinus]

It works deliciously