Need Minimize Button In Excel Form (VBA)

[krishnaoptif]

Hi,

I need minimize button on the excel form.

how can i do this??

Please suggest me....

 

[Jon von der Heyden]

Hi

I like this article that explains how.

 

[Leith Ross]

Hello Krishnaoptif,

Here is a simpler and more direct method. Copy this code to a standard VBA module in your workbook. Place a call to the macro inside the UserForm_Activate event. An example follows the code.

'Written: October 07, 2007
'Author:  Leith Ross
'Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000

Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120

 Private Declare Function GetWindowLong _
   Lib "user32.dll" _
    Alias "GetWindowLongA" _
     (ByVal hwnd As Long, _
      ByVal nIndex As Long) As Long
               
 Private Declare Function SetWindowLong _
  Lib "user32.dll" _
   Alias "SetWindowLongA" _
    (ByVal hwnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) As Long
     
'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
  Lib "user32.dll" _
   (ByVal hwnd As Long) As Long

'Returns the Window Handle of the Window accepting input
 Private Declare Function GetForegroundWindow _
  Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)

 Dim BitMask As Long
 Dim Window_Handle As Long
 Dim WindowStyle As Long
 Dim Ret As Long

   If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
      Window_Handle = GetForegroundWindow()
  
       WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
       BitMask = WindowStyle Or Box_Type
  
      Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
      Ret = DrawMenuBar(Window_Handle)
   End If

End Sub

Example of Calling the Macro

Private Sub UserForm_Activate()
  AddToForm MIN_BOX
End sub

 

[krishnaoptif]

Hi Heyden,

I am not able to understand that how should i use this code in form or modual.

 

[Leith Ross]

Hello krishnaoptif,

If you have a workbook, can you upload it to a file sharing site? It would easier to add the code to the workbook. You could then study it later.

 

[krishnaoptif]

Hi Leith, It's working fine..
but one more little query... If i am doing minimize the form after that i am not able to edit anything in same excel workbook.

I need --- If i am doing minimize the userform then i need to changes in excel workbook as well. workbook should be active to the changess.

 

[Leith Ross]

Hello krishnaoptif,

You need to change the UserForm property ShowModal to False. This will let you let you work on the worksheet while the form is displayed.

 

[krishnaoptif]

Thanks it's working fine

 

[erik.van.geit]

Hi,

I found a code in my libraries which is still shorter. Unfortunately I got it from an unknown source.

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLongA Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
 
Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Sub FormatUserForm(UserFormCaption As String)
Dim hwnd As Long
Dim exLong As Long
hwnd = FindWindowA(vbNullString, UserFormCaption)
exLong = GetWindowLongA(hwnd, -16)
If (exLong And &H20000) = 0 Then
SetWindowLongA hwnd, -16, exLong Or &H20000
Else
End If
End Sub

To be called from userform like this:

FormatUserForm (Me.Caption)

kind regards,
Erik

 

[Leith Ross]

Hello Erik,

The FindWindow function is not a reliable way to return the window handle of a form. While your code is shorter it is unfortunately not as reliable. When working with the API, taking shortcuts can crash or hang your computer.